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)
