You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Sean Hsuan-Yi Chu (JIRA)" <ji...@apache.org> on 2015/06/25 01:44:04 UTC
[jira] [Updated] (CALCITE-769) FilterProjectTransposeRule needs to
avoid filter being pushed down if there is "correlating variable" in the
condition
[ https://issues.apache.org/jira/browse/CALCITE-769?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Sean Hsuan-Yi Chu updated CALCITE-769:
--------------------------------------
Description:
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).
was:
The logical plan (prior to de-correlation) for the query in JdbcTest.testWithInsideWhereExists is
LogicalProject(deptno=[$1])
{color:red}LogicalFilter(condition=[IS NOT NULL($5)]){color}
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]])
We would like to push the red filter 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:
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])
{color:red}LogicalFilter(condition=[<=($0, $cor1.deptno)]){color}
LogicalFilter(condition=[>=($0, $4)])
LogicalJoin(condition=[true], joinType=[inner])
EnumerableTableScan(table=[[hr, depts]])
LogicalAggregate(group=[{0}])
LogicalProject(deptno=[$1])
EnumerableTableScan(table=[[hr, emps]])
The red filter contains the correlating variable, which will lead to an exception (stack trace given in the attachment).
> 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)