[
https://issues.apache.org/jira/browse/CALCITE-7257?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18039535#comment-18039535
]
weihua zhang commented on CALCITE-7257:
---------------------------------------
maybe same as SPARK-43780
> 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)
