[CALCITE-3975] Add options to ProjectFilterTransposeRule to push down project and filter expressions whole, not just field references

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.22.0
• Fix Version/s: 1.24.0
• Assignee: Julian Hyde
• Reporter: Steven Talbot
• Created: 06/May/20 22:03
• Updated: 25/Jul/20 15:00
• Resolved: 11/Jun/20 20:04

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.

Issue Links

• links to: PR 1999