[jira] [Commented] (CALCITE-5784) Generate the same correlationId for the same query

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

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