Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-1537

Unnecessary project expression in multi-sub-query plan

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.11.0
    • Component/s: core
    • Labels:

      Description

      Query

       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) 

      Plan just before calling SubqueryRemoveRule

      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]])
      

      Plan just after SubqueryRemoveRule

      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]])
      

      Plan just after decorrelation

      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]])
      

      As you can notice the top LogicalProject has unnecessary expression ENAME0 after decorrelation

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                julianhyde Julian Hyde
                Reporter:
                vgarg Vineet Garg
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: