[jira] [Commented] (CALCITE-5309) Accept cursors as inputs of table functions

["Bertil Chapuis (Jira)" <ji...@apache.org>]

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

Bertil Chapuis commented on CALCITE-5309:
-----------------------------------------

I spent a some time digging into this issue and here is a summary of my understanding of the issue:

The parser implements support for cursors and produces valid AST for the query in TableFunctionTest.testTableFunctionCursorInputs.
 
{code:java}
select n from table(s.process(2, cursor(select * from table(s.GenerateStrings(?))) )) as t(u) where u > 3{code}
The conversion of the AST to a logical plan fails, but this can be fixed by adding support for cursors in SqlTypeAssignementRule and JavaToSqlTypeConversionRules (see [draft PR|[https://github.com/apache/calcite/pull/3157/files]).] 
{code:java}
LogicalProject(U=[$0]): rowcount = 1.0, cumulative cost = {Infinity rows, Infinity cpu, Infinity io}, id = 9
  LogicalFilter(condition=[>($0, 3)]): rowcount = 1.0, cumulative cost = {Infinity rows, Infinity cpu, Infinity io}, id = 8
    LogicalTableFunctionScan(invocation=[process(2, CAST($0):CURSOR NOT NULL)], rowType=[RecordType(INTEGER result)], elementType=[class [Ljava.lang.Object;]): rowcount = 1.0, cumulative cost = {Infinity rows, Infinity cpu, Infinity io}, id = 7
      LogicalProject(n=[$0], s=[$1]): rowcount = 1.0, cumulative cost = {1.7976931348623157E308 rows, 1.7976931348623157E308 cpu, 1.7976931348623157E308 io}, id = 6
        LogicalTableFunctionScan(invocation=[GenerateStrings(?0)], rowType=[JavaType(class org.apache.calcite.util.Smalls$IntString)], elementType=[class org.apache.calcite.util.Smalls$IntString]): rowcount = 1.0, cumulative cost = {huge}, id = 0
 {code}
This logical plan is not supported by the enumerable adaptor and produces the following exception.
{code:java}
Error while preparing statement [select *
from table("s"."process"(2,
cursor(select * from table("s"."GenerateStrings"(?)))
)) as t(u)
where u > 3]
java.sql.SQLException: Error while preparing statement [select *
from table("s"."process"(2,
cursor(select * from table("s"."GenerateStrings"(?)))
)) as t(u)
where u > 3]

...

Caused by: java.lang.IllegalStateException: Unable to implement EnumerableCalc(expr#0=[{inputs}], expr#1=[3], expr#2=[>($t0, $t1)], result=[$t0], $condition=[$t2]): rowcount = 1.0, cumulative cost = {3.0 rows, 7.0 cpu, 0.0 io}, id = 47
  EnumerableTableFunctionScan(invocation=[process(2, CAST($0):CURSOR NOT NULL)], rowType=[RecordType(INTEGER result)], elementType=[class [Ljava.lang.Object;]): rowcount = 1.0, cumulative cost = {2.0 rows, 2.0 cpu, 0.0 io}, id = 43
    EnumerableTableFunctionScan(invocation=[GenerateStrings(?0)], rowType=[JavaType(class org.apache.calcite.util.Smalls$IntString)], elementType=[class org.apache.calcite.util.Smalls$IntString]): rowcount = 1.0, cumulative cost = {1.0 rows, 1.0 cpu, 0.0 io}, id = 35

...

Suppressed: java.lang.NullPointerException: inputGetter
        at java.base/java.util.Objects.requireNonNull(Objects.java:233)
        at org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitInputRef(RexToLixTranslator.java:1029)
        at org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitInputRef(RexToLixTranslator.java:101)
        at org.apache.calcite.rex.RexInputRef.accept(RexInputRef.java:113)

...{code}
In my understanding, a cursor should be a relational expression (i.e. a RelNode). Therefore, the next step should be to add a Cursor class to the o.a.c.rel.core package, a EnumerableCursor class to the o.a.c.adapter.enumarable package, and glue things together.

[~julianhyde] is this intuition correct?

 

> Accept cursors as inputs of table functions
> -------------------------------------------
>
>                 Key: CALCITE-5309
>                 URL: https://issues.apache.org/jira/browse/CALCITE-5309
>             Project: Calcite
>          Issue Type: New Feature
>            Reporter: Bertil Chapuis
>            Priority: Major
>
> The TableFunctionTest.testTableFunctionCursorsInputs is currently disabled and a note says that it could not be implemented. This feature is necessary to implement table functions that take a query or a table as an input.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)