[jira] [Commented] (CALCITE-769) FilterProjectTransposeRule needs to avoid filter being pushed down if there is "correlating variable" in the condition

["Sean Hsuan-Yi Chu (JIRA)" <ji...@apache.org>]

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

Sean Hsuan-Yi Chu commented on CALCITE-769:
-------------------------------------------

To resolve this, I am thinking about two possibilities:
1. We just do not initiate FilterProjectTransposeRule if there is correlating variable in the filter
2. There is nothing wrong to push filter with a correlating variable. The problem is de-correlation itself. 



> FilterProjectTransposeRule needs to avoid filter being pushed down if there is "correlating variable" in the condition
> ----------------------------------------------------------------------------------------------------------------------
>
>                 Key: CALCITE-769
>                 URL: https://issues.apache.org/jira/browse/CALCITE-769
>             Project: Calcite
>          Issue Type: Bug
>            Reporter: Sean Hsuan-Yi Chu
>            Assignee: Sean Hsuan-Yi Chu
>         Attachments: log.txt
>
>
> The logical plan (prior to de-correlation) for the query in JdbcTest.testWithInsideWhereExists is 
> {code}
> LogicalProject(deptno=[$1])
>   *LogicalFilter(condition=[IS NOT NULL($5)])
>     LogicalProject(empid=[$0], deptno=[$1], name=[$2], salary=[$3], commission=[$4], $f0=[$7])
>       LogicalJoin(condition=[AND(=($1, $5), =($1, $6))], joinType=[left])
>         EnumerableTableScan(table=[[hr, emps]])
>         LogicalAggregate(group=[{0, 1}], agg#0=[MIN($2)])
>           LogicalProject(deptno1=[$1], deptno0=[$2], $f0=[$0])
>             LogicalProject($f0=[true], deptno1=[$1], deptno0=[$2])
>               LogicalProject(EXPR$0=[1], deptno1=[$5], deptno0=[$4])
>                 LogicalFilter(condition=[<=($0, $5)])
>                   LogicalJoin(condition=[true], joinType=[inner])
>                     LogicalProject(deptno=[$0], name=[$1], employees=[$2], location=[$3], deptno0=[$4])
>                       LogicalFilter(condition=[>=($0, $4)])
>                         LogicalJoin(condition=[true], joinType=[inner])
>                           EnumerableTableScan(table=[[hr, depts]])
>                           LogicalAggregate(group=[{0}])
>                             LogicalProject(deptno=[$1])
>                               EnumerableTableScan(table=[[hr, emps]])
>                     LogicalAggregate(group=[{0}])
>                       LogicalProject(deptno=[$1])
>                         EnumerableTableScan(table=[[hr, emps]])
> {code}
> We would like to push the filter with asterisk below project in order to use FILTER_ON_JOIN rule to convert the left-join to inner-join.
> However, after we added FilterProjectTransposeRule. We got the plan:
> {code}
> LogicalProject(deptno=[$1])
>   LogicalProject(empid=[$0], deptno=[$1], name=[$2], salary=[$3], commission=[$4], $f0=[$6])
>     LogicalFilter(condition=[IS NOT NULL($6)])
>       LogicalJoin(condition=[=($1, $5)], joinType=[left])
>         EnumerableTableScan(table=[[hr, emps]])
>         LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
>           LogicalProject(deptno0=[$1], $f0=[$0])
>             LogicalProject($f0=[true], deptno0=[$1])
>               LogicalProject(EXPR$0=[1], deptno0=[$4])
>                 LogicalProject(deptno=[$0], name=[$1], employees=[$2], location=[$3], deptno0=[$4])
>                   *LogicalFilter(condition=[<=($0, $cor1.deptno)])
>                     LogicalFilter(condition=[>=($0, $4)])
>                       LogicalJoin(condition=[true], joinType=[inner])
>                         EnumerableTableScan(table=[[hr, depts]])
>                         LogicalAggregate(group=[{0}])
>                           LogicalProject(deptno=[$1])
>                             EnumerableTableScan(table=[[hr, emps]])
> {code}
> The filter with asterisk contains the correlating variable, which will lead to an exception (stack trace given in the attachment). 



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)