[
https://issues.apache.org/jira/browse/CALCITE-7661?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Darpan Lunagariya (e6data computing) reassigned CALCITE-7661:
-------------------------------------------------------------
Assignee: Darpan Lunagariya (e6data computing)
> RelDecorrelator loses shared correlation constraint across inner join inputs
> ----------------------------------------------------------------------------
>
> Key: CALCITE-7661
> URL: https://issues.apache.org/jira/browse/CALCITE-7661
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Darpan Lunagariya (e6data computing)
> Assignee: Darpan Lunagariya (e6data computing)
> Priority: Major
>
> h2. Problem
> When both inputs of an inner join reference the same outer correlated field,
> {{RelDecorrelator}} can lose the relationship between those inputs.
> For example:
> {code:sql}
> left.deptno = outer.deptno
> right.deptno = outer.deptno
> {code}
> After decorrelation, both inputs carry a copy of {{outer.deptno}}. Those
> copies must remain equal. Currently, the inner join can lose that equality,
> allowing rows associated with different outer values to be joined.
> h2. Reproducer
> Using the SCOTT schema:
> {code:sql}
> SELECT d.deptno
> FROM dept d
> WHERE EXISTS (
> SELECT *
> FROM (
> SELECT *
> FROM emp e
> WHERE e.deptno = d.deptno
> ) l
> JOIN (
> SELECT *
> FROM dept d2
> WHERE d2.deptno = d.deptno
> ) r
> ON TRUE
> )
> {code}
> Department 40 has no employees, so the original correlated query must not
> return it.
> h2. Incorrect plan
> {code}
> LogicalProject(DEPTNO=[$0])
> LogicalJoin(condition=[=($0, $3)], joinType=[inner])
> LogicalTableScan(table=[[scott, DEPT]])
> LogicalProject(DEPTNO3=[$0], $f1=[true])
> LogicalAggregate(group=[{0}])
> LogicalProject(DEPTNO3=[$12])
> LogicalJoin(condition=[true], joinType=[inner])
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
> HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], DEPTNO8=[$7])
> LogicalFilter(condition=[IS NOT NULL($7)])
> LogicalTableScan(table=[[scott, EMP]])
> LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2], DEPTNO3=[$0])
> LogicalTableScan(table=[[scott, DEPT]])
> {code}
> The inner join between EMP and DEPT has {{condition=[true]}}. Only the
> DEPT-side carrier, {{$12}}, is subsequently matched with the outer
> {{d.deptno}}. The EMP-side carrier, {{$8}}, is unconstrained, so an employee
> from another department can be joined with department 40.
> The inner join should instead retain equality between the two correlation
> carriers:
> {code}
> LogicalJoin(
> condition=[IS NOT DISTINCT FROM($8, $12)],
> joinType=[inner])
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
