[
https://issues.apache.org/jira/browse/CALCITE-3639?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17004302#comment-17004302
]
Rui Wang commented on CALCITE-3639:
-----------------------------------
Regarding to the mysql query test:
{code:sql}
mysql> select * from parts left join supply on parts.num = supply.pnum and
parts.num < 0 and supply.pnum < 0;
+------+-------------+------+------+
| num | orderOnhand | pnum | qty |
+------+-------------+------+------+
| 3 | 6 | NULL | NULL |
| 10 | 1 | NULL | NULL |
| 8 | 0 | NULL | NULL |
+------+-------------+------+------+
3 rows in set (0.00 sec)
{code}
I am not sure why there is still 3 rows in the result set as there is a
parts.num < 0 condition and all values in the num column is > 0?
> 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)
