[
https://issues.apache.org/jira/browse/CALCITE-7257?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
weihua zhang updated CALCITE-7257:
----------------------------------
Description:
* case1:
{code:java}
SELECT
E1.*
FROM
EMP E1
WHERE
E1.EMPNO = (
SELECT D1.DEPTNO FROM DEPT D1
WHERE E1.ENAME IN (
SELECT
B1.ENAME
FROM
BONUS B1
)
);
[Plan after conversion from Abstract Syntax Tree]
LogicalProject(empno=[$0], ename=[$1], job=[$2], mgr=[$3], hiredate=[$4],
sal=[$5], comm=[$6], deptno=[$7]), id = 12
LogicalFilter(condition=[=($0, $SCALAR_QUERY({
LogicalProject(DEPTNO=[$0])
LogicalFilter(condition=[IN($cor0.ename, {
LogicalProject(ENAME=[$0])
LogicalTableScan(table=[[testdb, bonus]])
})])
LogicalTableScan(table=[[testdb, dept]])
}))], variablesSet=[[$cor0]]), id = 10
LogicalTableScan(table=[[testdb, emp]]), id = 1
[Plan after subquery removal phase]
LogicalProject(empno=[$0], ename=[$1], job=[$2], mgr=[$3], hiredate=[$4],
sal=[$5], comm=[$6], deptno=[$7]), id = 16
LogicalProject(empno=[$0], ename=[$1], job=[$2], mgr=[$3], hiredate=[$4],
sal=[$5], comm=[$6], deptno=[$7]), id = 33
LogicalFilter(condition=[=($0, $8)]), id = 31
LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{1}]), id = 29
LogicalTableScan(table=[[testdb, emp]]), id = 1
LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)]), id = 27
LogicalProject(DEPTNO=[$0]), id = 25
LogicalProject(deptno=[$0], dname=[$1], loc=[$2]), id = 45
LogicalJoin(condition=[=($cor0.ename, $3)], joinType=[inner]), id
= 43 // <------ here
LogicalTableScan(table=[[testdb, dept]]), id = 3
LogicalAggregate(group=[{0}]), id = 41
LogicalProject(ENAME=[$0]), id = 39
LogicalTableScan(table=[[testdb, bonus]]), id = 5
[Plan after subquery decorrelation phase]
LogicalProject(empno=[$0], ename=[$1], job=[$2], mgr=[$3], hiredate=[$4],
sal=[$5], comm=[$6], deptno=[$7]), id = 91
LogicalProject(empno=[$0], ename=[$1], job=[$2], mgr=[$3], hiredate=[$4],
sal=[$5], comm=[$6], deptno=[$7]), id = 89
LogicalProject(empno=[$0], ename=[$1], job=[$2], mgr=[$3], hiredate=[$4],
sal=[$5], comm=[$6], deptno=[$7], $f0=[$8]), id = 107
LogicalFilter(condition=[=($0, $8)]), id = 104
LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{1}]), id = 98
LogicalTableScan(table=[[testdb, emp]]), id = 1
LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)]), id = 83
LogicalProject(DEPTNO=[$0]), id = 81
LogicalProject(deptno=[$0], dname=[$1], loc=[$2]), id = 79
LogicalJoin(condition=[=($cor0.ename, $3)], joinType=[inner]),
id = 77 // <----here
LogicalTableScan(table=[[testdb, dept]]), id = 3
LogicalAggregate(group=[{0}]), id = 75
LogicalProject(ENAME=[$0]), id = 73
LogicalTableScan(table=[[testdb, bonus]]), id = 5
{code}
* case2:
{code:java}
SELECT E1.ENAME
FROM EMP E1
WHERE
E1.SAL > (SELECT D1.DEPTNO FROM
DEPT D1 JOIN EMP E2
ON E1.DEPTNO = E2.DEPTNO
);
[Plan after conversion from Abstract Syntax Tree]
LogicalProject(ENAME=[$1]), id = 450
LogicalFilter(condition=[>($5, CAST($SCALAR_QUERY({
LogicalProject(DEPTNO=[$0])
LogicalJoin(condition=[=($cor0.deptno, $10)], joinType=[inner])
LogicalTableScan(table=[[testdb, dept]])
LogicalTableScan(table=[[testdb, emp]])
})):DOUBLE)], variablesSet=[[$cor0]]), id = 448
LogicalTableScan(table=[[testdb, emp]]), id = 439
[Plan after subquery removal phase]
LogicalProject(ENAME=[$1]), id = 454
LogicalProject(empno=[$0], ename=[$1], job=[$2], mgr=[$3], hiredate=[$4],
sal=[$5], comm=[$6], deptno=[$7]), id = 472
LogicalFilter(condition=[>($5, CAST($8):DOUBLE)]), id = 470
LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{7}]), id = 468
LogicalTableScan(table=[[testdb, emp]]), id = 439
LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)]), id = 466
LogicalProject(DEPTNO=[$0]), id = 464
LogicalJoin(condition=[=($cor0.deptno, $10)], joinType=[inner]), id
= 462
LogicalTableScan(table=[[testdb, dept]]), id = 441
LogicalTableScan(table=[[testdb, emp]]), id = 443
[Plan after subquery decorrelation phase]
LogicalProject(ENAME=[$1]), id = 506
LogicalProject(empno=[$0], ename=[$1], job=[$2], mgr=[$3], hiredate=[$4],
sal=[$5], comm=[$6], deptno=[$7]), id = 504
LogicalProject(empno=[$0], ename=[$1], job=[$2], mgr=[$3], hiredate=[$4],
sal=[$5], comm=[$6], deptno=[$7], $f0=[$8]), id = 522
LogicalFilter(condition=[>($5, CAST($8):DOUBLE)]), id = 519
LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{7}]), id = 513
LogicalTableScan(table=[[testdb, emp]]), id = 439
LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)]), id = 498
LogicalProject(DEPTNO=[$0]), id = 496
LogicalJoin(condition=[=($cor0.deptno, $10)], joinType=[inner]),
id = 494
LogicalTableScan(table=[[testdb, dept]]), id = 441
LogicalTableScan(table=[[testdb, emp]]), id = 443
{code}
was:
{code:java}
SELECT
E1.*
FROM
EMP E1
WHERE
E1.EMPNO = (
SELECT D1.DEPTNO FROM DEPT D1
WHERE E1.ENAME IN (
SELECT
B1.ENAME
FROM
BONUS B1
)
);
[Plan after conversion from Abstract Syntax Tree]
LogicalProject(empno=[$0], ename=[$1], job=[$2], mgr=[$3], hiredate=[$4],
sal=[$5], comm=[$6], deptno=[$7]), id = 12
LogicalFilter(condition=[=($0, $SCALAR_QUERY({
LogicalProject(DEPTNO=[$0])
LogicalFilter(condition=[IN($cor0.ename, {
LogicalProject(ENAME=[$0])
LogicalTableScan(table=[[testdb, bonus]])
})])
LogicalTableScan(table=[[testdb, dept]])
}))], variablesSet=[[$cor0]]), id = 10
LogicalTableScan(table=[[testdb, emp]]), id = 1
[Plan after subquery removal phase]
LogicalProject(empno=[$0], ename=[$1], job=[$2], mgr=[$3], hiredate=[$4],
sal=[$5], comm=[$6], deptno=[$7]), id = 16
LogicalProject(empno=[$0], ename=[$1], job=[$2], mgr=[$3], hiredate=[$4],
sal=[$5], comm=[$6], deptno=[$7]), id = 33
LogicalFilter(condition=[=($0, $8)]), id = 31
LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{1}]), id = 29
LogicalTableScan(table=[[testdb, emp]]), id = 1
LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)]), id = 27
LogicalProject(DEPTNO=[$0]), id = 25
LogicalProject(deptno=[$0], dname=[$1], loc=[$2]), id = 45
LogicalJoin(condition=[=($cor0.ename, $3)], joinType=[inner]), id
= 43 // <------ here
LogicalTableScan(table=[[testdb, dept]]), id = 3
LogicalAggregate(group=[{0}]), id = 41
LogicalProject(ENAME=[$0]), id = 39
LogicalTableScan(table=[[testdb, bonus]]), id = 5
[Plan after subquery decorrelation phase]
LogicalProject(empno=[$0], ename=[$1], job=[$2], mgr=[$3], hiredate=[$4],
sal=[$5], comm=[$6], deptno=[$7]), id = 91
LogicalProject(empno=[$0], ename=[$1], job=[$2], mgr=[$3], hiredate=[$4],
sal=[$5], comm=[$6], deptno=[$7]), id = 89
LogicalProject(empno=[$0], ename=[$1], job=[$2], mgr=[$3], hiredate=[$4],
sal=[$5], comm=[$6], deptno=[$7], $f0=[$8]), id = 107
LogicalFilter(condition=[=($0, $8)]), id = 104
LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{1}]), id = 98
LogicalTableScan(table=[[testdb, emp]]), id = 1
LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)]), id = 83
LogicalProject(DEPTNO=[$0]), id = 81
LogicalProject(deptno=[$0], dname=[$1], loc=[$2]), id = 79
LogicalJoin(condition=[=($cor0.ename, $3)], joinType=[inner]),
id = 77 // <----here
LogicalTableScan(table=[[testdb, dept]]), id = 3
LogicalAggregate(group=[{0}]), id = 75
LogicalProject(ENAME=[$0]), id = 73
LogicalTableScan(table=[[testdb, bonus]]), id = 5
{code}
> Subqueries cannot be decorrelated if join condition contains RexFieldAccess
> ---------------------------------------------------------------------------
>
> Key: CALCITE-7257
> URL: https://issues.apache.org/jira/browse/CALCITE-7257
> Project: Calcite
> Issue Type: Improvement
> Reporter: weihua zhang
> Priority: Major
>
> * case1:
> {code:java}
> SELECT
> E1.*
> FROM
> EMP E1
> WHERE
> E1.EMPNO = (
> SELECT D1.DEPTNO FROM DEPT D1
> WHERE E1.ENAME IN (
> SELECT
> B1.ENAME
> FROM
> BONUS B1
> )
> );
> [Plan after conversion from Abstract Syntax Tree]
> LogicalProject(empno=[$0], ename=[$1], job=[$2], mgr=[$3], hiredate=[$4],
> sal=[$5], comm=[$6], deptno=[$7]), id = 12
> LogicalFilter(condition=[=($0, $SCALAR_QUERY({
> LogicalProject(DEPTNO=[$0])
> LogicalFilter(condition=[IN($cor0.ename, {
> LogicalProject(ENAME=[$0])
> LogicalTableScan(table=[[testdb, bonus]])
> })])
> LogicalTableScan(table=[[testdb, dept]])
> }))], variablesSet=[[$cor0]]), id = 10
> LogicalTableScan(table=[[testdb, emp]]), id = 1
> [Plan after subquery removal phase]
> LogicalProject(empno=[$0], ename=[$1], job=[$2], mgr=[$3], hiredate=[$4],
> sal=[$5], comm=[$6], deptno=[$7]), id = 16
> LogicalProject(empno=[$0], ename=[$1], job=[$2], mgr=[$3], hiredate=[$4],
> sal=[$5], comm=[$6], deptno=[$7]), id = 33
> LogicalFilter(condition=[=($0, $8)]), id = 31
> LogicalCorrelate(correlation=[$cor0], joinType=[left],
> requiredColumns=[{1}]), id = 29
> LogicalTableScan(table=[[testdb, emp]]), id = 1
> LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)]), id = 27
> LogicalProject(DEPTNO=[$0]), id = 25
> LogicalProject(deptno=[$0], dname=[$1], loc=[$2]), id = 45
> LogicalJoin(condition=[=($cor0.ename, $3)], joinType=[inner]),
> id = 43 // <------ here
> LogicalTableScan(table=[[testdb, dept]]), id = 3
> LogicalAggregate(group=[{0}]), id = 41
> LogicalProject(ENAME=[$0]), id = 39
> LogicalTableScan(table=[[testdb, bonus]]), id = 5
> [Plan after subquery decorrelation phase]
> LogicalProject(empno=[$0], ename=[$1], job=[$2], mgr=[$3], hiredate=[$4],
> sal=[$5], comm=[$6], deptno=[$7]), id = 91
> LogicalProject(empno=[$0], ename=[$1], job=[$2], mgr=[$3], hiredate=[$4],
> sal=[$5], comm=[$6], deptno=[$7]), id = 89
> LogicalProject(empno=[$0], ename=[$1], job=[$2], mgr=[$3], hiredate=[$4],
> sal=[$5], comm=[$6], deptno=[$7], $f0=[$8]), id = 107
> LogicalFilter(condition=[=($0, $8)]), id = 104
> LogicalCorrelate(correlation=[$cor0], joinType=[left],
> requiredColumns=[{1}]), id = 98
> LogicalTableScan(table=[[testdb, emp]]), id = 1
> LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)]), id = 83
> LogicalProject(DEPTNO=[$0]), id = 81
> LogicalProject(deptno=[$0], dname=[$1], loc=[$2]), id = 79
> LogicalJoin(condition=[=($cor0.ename, $3)],
> joinType=[inner]), id = 77 // <----here
> LogicalTableScan(table=[[testdb, dept]]), id = 3
> LogicalAggregate(group=[{0}]), id = 75
> LogicalProject(ENAME=[$0]), id = 73
> LogicalTableScan(table=[[testdb, bonus]]), id = 5
> {code}
> * case2:
> {code:java}
> SELECT E1.ENAME
> FROM EMP E1
> WHERE
> E1.SAL > (SELECT D1.DEPTNO FROM
> DEPT D1 JOIN EMP E2
> ON E1.DEPTNO = E2.DEPTNO
> );
> [Plan after conversion from Abstract Syntax Tree]
> LogicalProject(ENAME=[$1]), id = 450
> LogicalFilter(condition=[>($5, CAST($SCALAR_QUERY({
> LogicalProject(DEPTNO=[$0])
> LogicalJoin(condition=[=($cor0.deptno, $10)], joinType=[inner])
> LogicalTableScan(table=[[testdb, dept]])
> LogicalTableScan(table=[[testdb, emp]])
> })):DOUBLE)], variablesSet=[[$cor0]]), id = 448
> LogicalTableScan(table=[[testdb, emp]]), id = 439
> [Plan after subquery removal phase]
> LogicalProject(ENAME=[$1]), id = 454
> LogicalProject(empno=[$0], ename=[$1], job=[$2], mgr=[$3], hiredate=[$4],
> sal=[$5], comm=[$6], deptno=[$7]), id = 472
> LogicalFilter(condition=[>($5, CAST($8):DOUBLE)]), id = 470
> LogicalCorrelate(correlation=[$cor0], joinType=[left],
> requiredColumns=[{7}]), id = 468
> LogicalTableScan(table=[[testdb, emp]]), id = 439
> LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)]), id = 466
> LogicalProject(DEPTNO=[$0]), id = 464
> LogicalJoin(condition=[=($cor0.deptno, $10)], joinType=[inner]),
> id = 462
> LogicalTableScan(table=[[testdb, dept]]), id = 441
> LogicalTableScan(table=[[testdb, emp]]), id = 443
> [Plan after subquery decorrelation phase]
> LogicalProject(ENAME=[$1]), id = 506
> LogicalProject(empno=[$0], ename=[$1], job=[$2], mgr=[$3], hiredate=[$4],
> sal=[$5], comm=[$6], deptno=[$7]), id = 504
> LogicalProject(empno=[$0], ename=[$1], job=[$2], mgr=[$3], hiredate=[$4],
> sal=[$5], comm=[$6], deptno=[$7], $f0=[$8]), id = 522
> LogicalFilter(condition=[>($5, CAST($8):DOUBLE)]), id = 519
> LogicalCorrelate(correlation=[$cor0], joinType=[left],
> requiredColumns=[{7}]), id = 513
> LogicalTableScan(table=[[testdb, emp]]), id = 439
> LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)]), id = 498
> LogicalProject(DEPTNO=[$0]), id = 496
> LogicalJoin(condition=[=($cor0.deptno, $10)],
> joinType=[inner]), id = 494
> LogicalTableScan(table=[[testdb, dept]]), id = 441
> LogicalTableScan(table=[[testdb, emp]]), id = 443
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
