You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Wang Yanlin (Jira)" <ji...@apache.org> on 2019/12/27 11:46:00 UTC
[jira] [Closed] (CALCITE-3639) JoinConditionPushRule fail to push
filter to inputs
[ https://issues.apache.org/jira/browse/CALCITE-3639?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Wang Yanlin closed CALCITE-3639.
--------------------------------
Resolution: Not A Bug
> JoinConditionPushRule fail to push filter to inputs
> ---------------------------------------------------
>
> Key: CALCITE-3639
> URL: https://issues.apache.org/jira/browse/CALCITE-3639
> Project: Calcite
> Issue Type: Bug
> Reporter: Wang Yanlin
> Assignee: Wang Yanlin
> Priority: Major
>
> The relnode(without applying optimize rules) for the sql
> {code:java}
> String sql = "select empno, emp.deptno from emp left join dept\n"
> + " on emp.deptno = dept.deptno and empno = 10 and dept.deptno = 20";
> {code}
> is
> {code:java}
> LogicalProject(EMPNO=[$0], DEPTNO=[$7])
> LogicalJoin(condition=[AND(=($7, $9), =($0, 10), =($9, 20))], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
>
> After optimized with *JoinConditionPushRule*, the relnode becomes
> {code:java}
> LogicalProject(EMPNO=[$0], DEPTNO=[$7])
> LogicalJoin(condition=[AND(=($7, $9), =($0, 10))], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalFilter(condition=[=($0, 20)])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> The optimize rule failed to push *empno = 10* to the left input, the better relnode should be
> {code:java}
> LogicalProject(EMPNO=[$0], DEPTNO=[$7])
> LogicalJoin(condition=[AND(=($7, $9))], joinType=[left])
> LogicalFilter(condition=[=($0, 10)])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalFilter(condition=[=($0, 20)])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
>
> Add this test case to reproduce
> {code:java}
> // RelOptRulesTest
> @Test public void testFilterInLeftJoin() {
> String sql = "select empno, emp.deptno from emp left join dept\n"
> + " on emp.deptno = dept.deptno and empno = 10 and dept.deptno = 20";
> sql(sql).withRule(FilterJoinRule.JOIN).check();
> }
> // data for this case
> <TestCase name="testFilterInLeftJoin">
> <Resource name="sql">
> <![CDATA[select empno, emps.deptno from emps left join depts
> on emps.deptno = depts.deptno and empno = 10 and depts.deptno = 20]]>
> </Resource>
> <Resource name="planBefore">
> <![CDATA[
> LogicalProject(EMPNO=[$0], DEPTNO=[$7])
> LogicalJoin(condition=[AND(=($7, $9), =($0, 10), =($9, 20))], joinType=[left])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> ]]>
> </Resource>
> <Resource name="planAfter">
> <![CDATA[
> LogicalProject(EMPNO=[$0], DEPTNO=[$7])
> LogicalJoin(condition=[AND(=($7, $9))], joinType=[left])
> LogicalFilter(condition=[=($0, 10)])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalFilter(condition=[=($0, 20)])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> ]]>
> </Resource>
> </TestCase>
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)