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