weihua zhang created CALCITE-7303:
-------------------------------------
Summary: Subqueries cannot be decorrelated if filter condition
have multi CorrelationId
Key: CALCITE-7303
URL: https://issues.apache.org/jira/browse/CALCITE-7303
Project: Calcite
Issue Type: Bug
Reporter: weihua zhang
{code:sql}
SELECT deptno
FROM emp e
WHERE EXISTS
(SELECT *
FROM dept d
WHERE EXISTS
(SELECT *
FROM bonus ea
WHERE ea.ENAME = e.ENAME
AND d.deptno = e.deptno));
{code}
{code:java}
[Plan after subquery removal phase]
LogicalProject(DEPTNO=[$7]), id = 607
LogicalProject(empno=[$0], ename=[$1], job=[$2], mgr=[$3], hiredate=[$4],
sal=[$5], comm=[$6], deptno=[$7]), id = 622
LogicalCorrelate(correlation=[$cor0], joinType=[inner],
requiredColumns=[{1, 7}]), id = 620
LogicalTableScan(table=[[testdb, emp]]), id = 587
LogicalAggregate(group=[{0}]), id = 618
LogicalProject(i=[true]), id = 616
LogicalProject(deptno=[$0], dname=[$1], loc=[$2]), id = 637
LogicalCorrelate(correlation=[$cor1], joinType=[inner],
requiredColumns=[{0}]), id = 635
LogicalTableScan(table=[[testdb, dept]]), id = 589
LogicalAggregate(group=[{0}]), id = 633
LogicalProject(i=[true]), id = 631
LogicalFilter(condition=[AND(=($0, $cor0.ename),
=($cor1.deptno, $cor0.deptno))]), id = 629
LogicalTableScan(table=[[testdb, bonus]]), id = 591
{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
