[jira] [Comment Edited] (CALCITE-3975) ProjectFilterTransposeRule should succeed for project that happens to reference all input columns

["Steven Talbot (Jira)" <ji...@apache.org>]

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

Steven Talbot edited comment on CALCITE-3975 at 6/1/20, 11:13 PM:
------------------------------------------------------------------

I see. That all makes sense. To me at first, before we got deep into this, the most straightforward reading of what "ProjectFilterTransposeRule" would do, in terms of behaving similarly to other "transpose" rules, would be to transpose only if the filter was on an expression in the Project, and otherwise do nothing. In other words, something like
{noformat}
SELECT sal + comm AS remuneration, depto
 FROM Emp
 WHERE deptno = 10{noformat}
becomes 
{noformat}
SELECT *
 FROM (
 SELECT sal + comm AS remuneration, deptno AS predicate
 FROM Emp)
 WHERE deptno = 10{noformat}
and then other expressions like yours above that do not have such properties get left alone.

 

But I see now that would be a pretty radically different rule.


was (Author: swtalbot):
I see. That all makes sense. To me at first, before we got into this, the most straightforward reading of what "ProjectFilterTransposeRule" would do, in terms of behaving similarly to other "transpose" rules, would be to transpose only if the filter was on an expression in the Project, and otherwise do nothing. In other words, something like
{noformat}
SELECT sal + comm AS remuneration, depto
 FROM Emp
 WHERE deptno = 10{noformat}

 becomes 
{noformat}
SELECT *
 FROM (
 SELECT sal + comm AS remuneration, deptno AS predicate
 FROM Emp)
 WHERE deptno = 10{noformat}

 and then other expressions like yours above that do not have such properties get left alone.

 

But I see now that would be a pretty radically different rule.

> ProjectFilterTransposeRule should succeed for project that happens to reference all input columns
> -------------------------------------------------------------------------------------------------
>
>                 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
>
> ... 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)