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

Unnecessary project expression in multi-sub-query plan

Rank to TopRank to BottomBulk Copy AttachmentsBulk Move AttachmentsVotersWatch issueWatchersConvert to sub-taskLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • None
    • 1.11.0
    • core

    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

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

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

            Dates

              Created:
              Updated:
              Resolved:

              Slack

                Issue deployment