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

Add options to ProjectFilterTransposeRule to push down project and filter expressions whole, not just field references

    XMLWordPrintableJSON

    Details

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

      Description

      ProjectFilterTransposeRule should succeed for project that happens to reference all input columns. That is, I think if I make the trivial fix of just "only skip trivial projects", something like 

      && origProj.getProjects().stream().allMatch((proj) -> proj instanceof RexInputRef) 

      at https://github.com/apache/calcite/blob/571731b80a58eb095ebac7123285c375e7afff90/core/src/main/java/org/apache/calcite/rel/rules/PushProjector.java#L354 HepPlanner goes into infinite recursion with the rule.

      But here's the test case:

       

      @Test public void testPushProjectPastFilter3() {
        final String sql = "select empno + deptno, ename, job, mgr, hiredate, sal, comm, slacker from emp where sal = 10 * comm\n"
            + "and upper(ename) = 'FOO'";
        sql(sql).withRule(ProjectFilterTransposeRule.INSTANCE).check();
      }
      

       

       

       

          <TestCase name="testPushProjectPastFilter3">
              <Resource name="sql">
                  <![CDATA[select empno + deptno, emp* from emp where sal = 10 * comm
      and upper(ename) = 'FOO']]>
              </Resource>
              <Resource name="planBefore">
                  <![CDATA[
      LogicalProject(EXPR$0=[+($0, $7)], EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
        LogicalFilter(condition=[AND(=($5, *(10, $6)), =(UPPER($1), 'FOO'))])
          LogicalTableScan(table=[[CATALOG, SALES, EMP]])
      ]]>
              </Resource>
              <Resource name="planAfter">
                  <![CDATA[
      -- note: probably a LogicalProject here, although see below comment
      LogicalFilter(condition=[AND(=($5, *(10, $6)), =(UPPER($1), 'FOO'))])
        LogicalProject(EXPR$0=[+($0, $7)], EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
          LogicalTableScan(table=[[CATALOG, SALES, EMP]])
      ]]>
              </Resource>
          </TestCase>

       

      There's no reason the rule shouldn't succeed here, right? Or am I missing something?

      The reason this rule goes into an infinite recursion with hepplanner is because it sticks a project on top after transpose to handle common expressions extracted from the filter and the project. Ideally, it could have a mode where it could avoid doing that and do a true "transpose" if there was no need for it. For example, I don't think there is a a need for a reproject on top in this test case: you can just transpose and everything works as it should. This would be another way to avoid infinite recursion.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                julianhyde Julian Hyde
                Reporter:
                swtalbot Steven Talbot
              • Votes:
                0 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 20m
                  20m