[
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}
{code:java}
[Plan after subquery decorrelation phase]
LogicalProject(DEPTNO=[$7]), id = 259
LogicalJoin(condition=[AND(=($1, $8), =($7, $9))], joinType=[inner]), id = 257
LogicalTableScan(table=[[testdb, emp]]), id = 119
LogicalProject(ename=[$0], $f4=[$1], $f2=[true]), id = 255
LogicalAggregate(group=[{0, 1}]), id = 253
LogicalProject(ename=[$3], $f4=[$4]), id = 251
LogicalJoin(condition=[=($0, $5)], joinType=[inner]), id = 249
LogicalTableScan(table=[[testdb, dept]]), id = 121
LogicalProject(ename=[$0], $f4=[$1], $f5=[$2], $f3=[true]), id = 247
LogicalAggregate(group=[{0, 1, 2}]), id = 245
LogicalProject(ename=[$0], $f4=[$4], $f5=[$5]), id = 243
LogicalFilter(condition=[AND(=($5, $4), IS NOT NULL($0))]),
id = 241
LogicalProject(ename=[$0], job=[$1], sal=[$2], comm=[$3],
$f4=[$cor1.deptno], $f5=[$cor0.deptno]), id = 239
LogicalTableScan(table=[[testdb, bonus]]), id = 123
{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 // here
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}
> {code:java}
> [Plan after subquery decorrelation phase]
> LogicalProject(DEPTNO=[$7]), id = 259
> LogicalJoin(condition=[AND(=($1, $8), =($7, $9))], joinType=[inner]), id =
> 257
> LogicalTableScan(table=[[testdb, emp]]), id = 119
> LogicalProject(ename=[$0], $f4=[$1], $f2=[true]), id = 255
> LogicalAggregate(group=[{0, 1}]), id = 253
> LogicalProject(ename=[$3], $f4=[$4]), id = 251
> LogicalJoin(condition=[=($0, $5)], joinType=[inner]), id = 249
> LogicalTableScan(table=[[testdb, dept]]), id = 121
> LogicalProject(ename=[$0], $f4=[$1], $f5=[$2], $f3=[true]), id =
> 247
> LogicalAggregate(group=[{0, 1, 2}]), id = 245
> LogicalProject(ename=[$0], $f4=[$4], $f5=[$5]), id = 243
> LogicalFilter(condition=[AND(=($5, $4), IS NOT NULL($0))]),
> id = 241
> LogicalProject(ename=[$0], job=[$1], sal=[$2], comm=[$3],
> $f4=[$cor1.deptno], $f5=[$cor0.deptno]), id = 239
> LogicalTableScan(table=[[testdb, bonus]]), id = 123
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
