[
https://issues.apache.org/jira/browse/CALCITE-7898?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18133428#comment-18133428
]
Weihua Zhang commented on CALCITE-7898:
---------------------------------------
maybe fixed in CALCITE-7882
> SubQueryRemoveRule corrupts plans when a join condition holds multiple
> correlated sub-queries sharing one correlation id
> ------------------------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-7898
> URL: https://issues.apache.org/jira/browse/CALCITE-7898
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.42.0
> Reporter: Darpan Lunagariya (e6data)
> Assignee: Darpan Lunagariya (e6data)
> Priority: Major
>
> {{JoinSubQueryToCorrelateRule}} ({{SubQueryRemoveRule.matchJoin}}) removes
> correlated sub-queries from a {{Join}} condition one at a time. Each removal
> wraps the input in a new {{LogicalCorrelate}}. When two or more correlated
> sub-queries in the same condition use the *same* correlation id (e.g. both
> reference {{$cor0}} from the join), the rewrites all reuse that id. The
> result is two nested {{Correlate}} nodes bound to the same id, so the inner
> binding shadows the outer and the sub-queries read the wrong correlated
> value. The produced plan is semantically wrong.
> Two problems combine:
> # The id is not freshened per rewrite, so nested {{Correlate}}s collide on
> one id.
> # The intermediate {{Join}} rebuilt between rewrites drops the correlation
> ids still needed by the sub-queries that have not been removed yet (its
> {{variablesSet}} is not carried over), so later rewrites lose their
> correlation scope.
> This is the join-condition analogue of CALCITE-7753
> ({{CorrelateProjectExtractor corrupts plans with nested Correlates that reuse
> the same correlation id}}).
> h2. Reproducer
> {code:sql}
> SELECT e.empno FROM emp e JOIN dept d
> ON e.deptno = d.deptno AND (
> EXISTS (SELECT 1 FROM emp e2 WHERE e2.mgr = e.empno AND e2.sal > 10)
> OR EXISTS (SELECT 1 FROM emp e3 WHERE e3.mgr = e.empno AND e3.sal > 20))
> {code}
> Plan it generates:
> {code:java}
> LogicalProject(EMPNO=[$0])
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
> SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$10], NAME=[$11])
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
> SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], i=[$9], DEPTNO0=[$10],
> NAME=[$11])
> LogicalJoin(condition=[AND(=($7, $10), OR(IS NOT NULL($9), IS NOT
> NULL($12)))], joinType=[inner])
> LogicalCorrelate(correlation=[$cor0], joinType=[left],
> requiredColumns=[{0}])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalAggregate(group=[{0}])
> LogicalProject(i=[true])
> LogicalFilter(condition=[AND(=($3, $cor0.EMPNO), >($5, 10))])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalJoin(condition=[true], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> LogicalAggregate(group=[{0}])
> LogicalProject(i=[true])
> LogicalFilter(condition=[AND(=($3, $cor0.EMPNO), >($5, 20))])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> Both {{EXISTS}} sub-queries correlate on {{$cor0}} (the join row). The
> rewrite emits two stacked {{LogicalCorrelate}} nodes bound to the same id
> instead of distinct ids, so the second {{EXISTS}} evaluates against the wrong
> correlation binding.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
