Vineet Garg created CALCITE-1537: ------------------------------------ Summary: Un-necessary project expression in multi-subquery plan Key: CALCITE-1537 URL: https://issues.apache.org/jira/browse/CALCITE-1537 Project: Calcite Issue Type: Bug Components: core Reporter: Vineet Garg Assignee: Julian Hyde
Query {code} select sal from emp where empno IN (select deptno from dept where emp.job = dept.name) AND empno IN (select empno from emp e where emp.ename = e.ename) {code} Plan just before calling *SubqueryRemoveRule* {code} LogicalProject(SAL=[$5]) LogicalFilter(condition=[AND(IN($0, { LogicalProject(DEPTNO=[$0]) LogicalFilter(condition=[=($cor0.JOB, $1)]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) }), IN($0, { LogicalProject(EMPNO=[$0]) LogicalFilter(condition=[=($cor0.ENAME, $1)]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) }))], variablesSet=[[$cor0]]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) {code} Plan just after *SubqueryRemoveRule* {code} LogicalProject(SAL=[$5]) LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$9]) LogicalJoin(condition=[=($0, $10)], joinType=[inner]) LogicalFilter(condition=[=($0, $9)]) LogicalCorrelate(correlation=[$cor0], joinType=[INNER], requiredColumns=[{2}]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) LogicalAggregate(group=[{0}]) LogicalProject(DEPTNO=[$0]) LogicalFilter(condition=[=($cor0.JOB, $1)]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) LogicalAggregate(group=[{0}]) LogicalProject(EMPNO=[$0]) LogicalFilter(condition=[=($cor0.ENAME, $1)]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) {code} Plan just after *decorrelation* {code} LogicalProject(SAL=[$5], ENAME0=[$9]) LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], ENAME0=[$10]) LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$9], ENAME0=[$12]) LogicalJoin(condition=[=($0, $11)], joinType=[inner]) LogicalJoin(condition=[AND(=($2, $10), =($0, $9))], joinType=[inner]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) LogicalAggregate(group=[{0, 1}]) LogicalProject(DEPTNO=[$0], JOB=[$1]) LogicalProject(DEPTNO=[$0], JOB=[$2]) LogicalJoin(condition=[=($2, $1)], joinType=[inner]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) LogicalAggregate(group=[{0}]) LogicalProject(JOB=[$2]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) LogicalAggregate(group=[{0, 1}]) LogicalProject(EMPNO=[$0], ENAME0=[$1]) LogicalProject(EMPNO=[$0], ENAME0=[$9]) LogicalJoin(condition=[=($9, $1)], joinType=[inner]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) LogicalAggregate(group=[{0}]) LogicalProject(ENAME=[$1]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) {code} As you can notice the top *LogicalProject* has unnecessary expression *ENAME0* after decorrelation -- This message was sent by Atlassian JIRA (v6.3.4#6332)