[
https://issues.apache.org/jira/browse/CALCITE-3975?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17121395#comment-17121395
]
Steven Talbot commented on CALCITE-3975:
----------------------------------------
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
SELECT sal + comm AS remuneration, depto
FROM Emp
WHERE deptno = 10
becomes
SELECT *
FROM (
SELECT sal + comm AS remuneration, deptno AS predicate
FROM Emp)
WHERE deptno = 10
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)
