[
https://issues.apache.org/jira/browse/CALCITE-7303?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
weihua zhang updated CALCITE-7303:
----------------------------------
Description:
{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 // here
LogicalTableScan(table=[[testdb, bonus]]), id = 591
{code}
was:
{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}
> 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
> Priority: Major
>
> {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 // here
> LogicalTableScan(table=[[testdb, bonus]]), id = 591
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
