[jira] [Comment Edited] (CALCITE-4521) Support User Defined Table-valued Function

["Julian Hyde (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-4521?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17300753#comment-17300753 ] 

Julian Hyde edited comment on CALCITE-4521 at 3/13/21, 7:40 AM:
----------------------------------------------------------------

If a user-defined function (UDF) returns a table I guess people call it a "user-defined table-valued function" (UDTVF); however, I think a UDTVF is just a kind of UDF:
* a UDF can return a scalar or a table;
* any argument to a UDF can be a scalar or a table;
* "TABLE x" as shorthand for "SELECT * FROM x" has been in SQL for a long time (albeit little used) and is not exclusive to arguments to functions.

The novel thing in your above example (UDTVF) seems to be how the function derives the row type of the table that is returned. (I couldn't tell from your example whether the table always has row type (in1 INTEGER, in2 VARCHAR), or whether you can specify a different row type for each call to the function. And is {{TableValuedFunction}} some new class or interface you propose to add to Calcite?)


was (Author: julianhyde):
If a user-defined function (UDF) returns a table I guess people call it a "user-defined table-valued function" (UDTVF), I don't think they're different:
* a UDF can return a scalar or a table;
* any argument to a UDF can be a scalar or a table;
* "TABLE x" as shorthand for "SELECT * FROM x" has been in SQL for a long time (albeit little used) and is not exclusive to arguments to functions.

The novel thing in the above example (UDTVF) seems to be how the function derives the row type of the table that is returned. (I couldn't tell from your example whether the table always has row type (in1 INTEGER, in2 VARCHAR), or whether you can specify a different row type for each call to the function. And is {{TableValuedFunction}} some new class or interface you propose to add to Calcite?)

> Support User Defined Table-valued Function
> ------------------------------------------
>
>                 Key: CALCITE-4521
>                 URL: https://issues.apache.org/jira/browse/CALCITE-4521
>             Project: Calcite
>          Issue Type: Improvement
>          Components: core
>            Reporter: herodu
>            Priority: Major
>
> It already support TUMBLE/HOP/SESSION windowing table-valued function in current version. But in fact, the table-valued function is not just the current features, and we want to support user defined table-valued function, just like the following query:
> {code:java}
> select * from table(UDTVF(table Shipments))
> {code}
> or
> {code:java}
> select * from table(UDTVF(select * from Shipments)){code}
> The input parameters of 'UDTVF' is the output rowtype of the inner subquery, and the output of 'UDTVF' is defined by users, just like: 
> {code:java}
> final TableValuedFunction tableValuedFunction =
>     (RelDataTypeFactory typeFactory) -> typeFactory.builder()
>         .add("in1", SqlTypeName.INTEGER)
>         .add("in2", SqlTypeName.VARCHAR)
>         .build();
> {code}
>  
> And the expected output logic plan like: 
> {code:java}
> LogicalProject(in1=[$0], in2=[$1])
>   LogicalTableFunctionScan(invocation=[UDTVF($0, $1)], rowType=[RecordType(INTEGER in1,VARCHAR in2)])
>     LogicalProject(ORDERID=[$0], ROWTIME=[$1])
>       LogicalTableScan(table=[[CATALOG, SALES, SHIPMENTS]])
> {code}
>  
>  



--
This message was sent by Atlassian Jira
(v8.3.4#803005)