[CALCITE-6204] When do not expand the plan when SqlToRel, the final plan with SET_SEMANTICS_TABLE is wrong - ASF JIRA

XML

Word

Printable

JSON

Details

Type: Bug
Status: Open
Priority: Major
Resolution: Unresolved
Affects Version/s: None
Fix Version/s: None
Component/s: None
Labels:
None

Description

Modify the test SqlToRelConverterTest#testTableFunctionWithPartitionKey to reproduce this bug:

@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();
}

The final plan is like:

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)])

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`.

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

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.

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

2.1
SqlCall before entering StandardConvertletTable