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

Enhance SubQueryRemoveRule to rewrite IN operator with the constant from the left side more optimally

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.17.0
    • Component/s: None
    • Labels:
      None

      Description

      Currently, for queries like this:

      select sal from emp where 2 IN (select deptno from dept)
      

      SubQueryRemoveRule rules expand query plan in such a way:

      LogicalProject(SAL=[$5])
        LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
          LogicalJoin(condition=[=(2, $9)], joinType=[inner])
            LogicalTableScan(table=[[CATALOG, SALES, EMP]])
            LogicalProject(DEPTNO=[$0])
              LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
      

      Since join condition is actually a filter condition, it will be pushed into the filter during further planning stages and join with the true condition is left.

      But these types of the queries may be rewritten in the same way as EXISTS queries:

      select sal from emp where EXISTS (select deptno from dept where deptno=2)
      

      with the more optimal plan:

      LogicalProject(SAL=[$5])
        LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
          LogicalJoin(condition=[true], joinType=[inner])
            LogicalTableScan(table=[[CATALOG, SALES, EMP]])
            LogicalAggregate(group=[{0}])
              LogicalProject(i=[true])
                LogicalFilter(condition=[=($0, 2)])
                  LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
      

        Attachments

          Activity

            People

            • Assignee:
              julianhyde Julian Hyde
              Reporter:
              vvysotskyi Volodymyr Vysotskyi
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: