[ https://issues.apache.org/jira/browse/CALCITE-3639?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Wang Yanlin reassigned CALCITE-3639: ------------------------------------ Assignee: Wang Yanlin > 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)