[jira] [Created] (CALCITE-1537) Un-necessary project expression in multi-subquery plan

["Vineet Garg (JIRA)" <ji...@apache.org>]

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)