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)
