[
https://issues.apache.org/jira/browse/CALCITE-7661?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Darpan Lunagariya (e6data computing) updated CALCITE-7661:
----------------------------------------------------------
Description:
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}
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 this query
will return the department for which there are no employees, which is wrong.
The inner join should instead retain equality between the two correlation
carriers:
{code}
LogicalJoin(
condition=[IS NOT DISTINCT FROM($8, $12)],
joinType=[inner])
{code}
was:
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}
> 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}
> 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 this query
> will return the department for which there are no employees, which is wrong.
> 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)
