[jira] [Created] (CALCITE-6204) When do not expand the plan when SqlToRel, the final plan with SET_SEMANTICS_TABLE is wrong

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

xuyang created CALCITE-6204:
-------------------------------

             Summary: When do not expand the plan when SqlToRel, the final plan with SET_SEMANTICS_TABLE is wrong   
                 Key: CALCITE-6204
                 URL: https://issues.apache.org/jira/browse/CALCITE-6204
             Project: Calcite
          Issue Type: Bug
            Reporter: xuyang
         Attachments: image-2024-01-16-10-12-37-047.png, image-2024-01-16-10-14-04-036.png, image-2024-01-16-10-15-20-310.png, image-2024-01-16-10-16-54-471.png

Modify the test SqlToRelConverterTest#testTableFunctionWithPartitionKey to reproduce this bug:

 
{code:java}
@Test void testTableFunctionWithPartitionKey() {
  final String sql = "select *\n"
      + "from table(topn(table orders partition by productid, 3))";
  sql(sql).withConfig(c -> c.withExpand(false)).ok();
} {code}
The final plan is like:

 

 
{code:java}
LogicalProject(ROWTIME=[$0], PRODUCTID=[$1], ORDERID=[$2], RANK_NUMBER=[$3])
  LogicalTableFunctionScan(invocation=[TOPN(3)], rowType=[RecordType(TIMESTAMP(0) ROWTIME, INTEGER PRODUCTID, INTEGER ORDERID, BIGINT RANK_NUMBER)])
 {code}
We can see that in the final plan the TableFunctionScan lost the partition keys and lost the subquery that contains the original table source `orders`.

=========================================================

{color:#de350b}The following is my attempt to find out where the bug lies. Some pictures with variables for some key steps are attached to make it easier for others to understand.{color}

When I dug into the code, I found that the problem may be in the following steps:

  1. When not expanding plan, we will retain subquery in SqlToRelConverter#convertCollectionTable 

  2. When converting the SqlCall of the "topn" function in StandardConvertletTable, the first SET_SEMANTICS_TABLE operand will be forcibly ignored, so only the arg "3" in the "topn" function is retained, and the original table "order" and partition key are removed. For details, please refer to StandardConvertletTable#convertOperands 

  3. After converting the expression with "topn" SqlCall to RexCall, when building LogicalTableFunctionScan, we only use the RexCall that does not contain the original table "order" and partition key, and the complete subquery retained by step 1 is not used..

The plan for the relevant steps is as follows:
1. Subquery retained when bb is not expanded

!image-2024-01-16-10-12-37-047.png|width=656,height=290!
2.1
SqlCall before entering StandardConvertletTable

!image-2024-01-16-10-14-04-036.png|width=653,height=183!

2.2
RexCall after StandardConvertletTable processing

!image-2024-01-16-10-15-20-310.png|width=578,height=146!

3. The constructed LogicalTableFunctionScan

!image-2024-01-16-10-16-54-471.png|width=682,height=262!

 

 



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