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

["Julian Hyde (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-3975?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17124399#comment-17124399 ] 

Julian Hyde commented on CALCITE-3975:
--------------------------------------

I had the same misunderstanding - I expected the rule to push down expressions, not field references.

In [PR 1999|https://github.com/apache/calcite/pull/1999] I have added new parameters {{boolean wholeProject}} and {{boolean wholeFilter}} to {{ProjectFilterTransposeRule}}. With these parameters set, it pushes down expressions. Please review.

> Add options to ProjectFilterTransposeRule to push down project and filter expressions whole, not just field references
> ----------------------------------------------------------------------------------------------------------------------
>
>                 Key: CALCITE-3975
>                 URL: https://issues.apache.org/jira/browse/CALCITE-3975
>             Project: Calcite
>          Issue Type: Bug
>    Affects Versions: 1.22.0
>            Reporter: Steven Talbot
>            Assignee: Julian Hyde
>            Priority: Major
>          Time Spent: 10m
>  Remaining Estimate: 0h
>
> 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 
> {noformat}
> && origProj.getProjects().stream().allMatch((proj) -> proj instanceof RexInputRef) {noformat}
> at [https://github.com/apache/calcite/blob/571731b80a58eb095ebac7123285c375e7afff90/core/src/main/java/org/apache/calcite/rel/rules/PushProjector.java#L354 |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:
>  
> {code:java}
> @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();
> }
> {code}
>  
>  
>  
> {noformat}
>     <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>{noformat}
>  
> 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.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)