[
https://issues.apache.org/jira/browse/CALCITE-3639?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17004120#comment-17004120
]
Wang Yanlin commented on CALCITE-3639:
--------------------------------------
Actually, this is not a bug.
{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}
is not equal to
{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}
Just conside the case below
{noformat}
mysql> select * from parts;
+------+-------------+
| num | orderOnhand |
+------+-------------+
| 3 | 6 |
| 10 | 1 |
| 8 | 0 |
+------+-------------+
3 rows in set (0.00 sec)
mysql> select * from supply;
+------+------+
| pnum | qty |
+------+------+
| 3 | 4 |
| 3 | 2 |
| 10 | 1 |
+------+------+
3 rows in set (0.00 sec)
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)
{noformat}
It's left join, and the filter in on condition, push it to left will reduce the
row number.
I'll just close this PR.
> 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)
