weihua zhang created CALCITE-7257:
-------------------------------------
Summary: 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
{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
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
LogicalTableScan(table=[[testdb, dept]]), id = 3
LogicalAggregate(group=[{0}]), id = 75
LogicalProject(ENAME=[$0]), id = 73
LogicalTableScan(table=[[testdb, bonus]]), id = 5
{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
