Darpan Lunagariya (e6data) created CALCITE-7898:
---------------------------------------------------
Summary: 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)
{{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)
