Wang Yanlin created CALCITE-3639: ------------------------------------ Summary: 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
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)