[ https://issues.apache.org/jira/browse/CALCITE-5784?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17733048#comment-17733048 ]
Aitozi commented on CALCITE-5784: --------------------------------- IMO, we could deduplicate the creation of the correation id during the sql2rel phase. Same identifier in the same scope can share the same id. > Generate the same correlationId for the same query > -------------------------------------------------- > > Key: CALCITE-5784 > URL: https://issues.apache.org/jira/browse/CALCITE-5784 > Project: Calcite > Issue Type: Improvement > Components: core > Reporter: Aitozi > Priority: Major > > Currently, the CTE query block will be sql2rel multiple times if be > referenced multiple times. If it contains a lateral join, it will generate > different correlation id. See below: > {code:java} > String sql = "WITH a AS (SELECT ename, job, empno, r FROM emp, LATERAL > TABLE (ramp(empno)) as T(r))" > + " SELECT * from a a1, a a2 WHERE a1.r = a2.empno"; > sql(sql).ok(); > {code} > {code:java} > LogicalProject(ENAME=[$0], JOB=[$1], EMPNO=[$2], R=[$3], ENAME0=[$4], > JOB0=[$5], EMPNO0=[$6], R0=[$7]) > LogicalJoin(condition=[=($3, $6)], joinType=[inner]) > LogicalProject(ENAME=[$1], JOB=[$2], EMPNO=[$0], R=[$9]) > LogicalCorrelate(correlation=[$cor0], joinType=[inner], > requiredColumns=[{0}]) > LogicalTableScan(table=[[CATALOG, SALES, EMP]]) > LogicalTableFunctionScan(invocation=[RAMP($cor0.EMPNO)], > rowType=[RecordType(INTEGER I)]) > LogicalProject(ENAME=[$1], JOB=[$2], EMPNO=[$0], R=[$9]) > LogicalCorrelate(correlation=[$cor1], joinType=[inner], > requiredColumns=[{0}]) > LogicalTableScan(table=[[CATALOG, SALES, EMP]]) > LogicalTableFunctionScan(invocation=[RAMP($cor1.EMPNO)], > rowType=[RecordType(INTEGER I)]) > {code} > It will generate two correlation id. In flink there is a subplan reuse based > on the digest, it will break this functionality -- This message was sent by Atlassian Jira (v8.20.10#820010)