[
https://issues.apache.org/jira/browse/CALCITE-457?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14200978#comment-14200978
]
Jinfeng Ni commented on CALCITE-457:
------------------------------------
Drill hits the same problem when there is a compound expression (not a single
column) in non-ansi join condition, and we fixed this problem in Drill's forked
version of Optiq. Basically, the cause of this problem is the the compound
expression in FILTER is not pushed past join, leaving the JOIN operator with
"true" condition.
In contrast, if the compound expression is in "ON" clause (ansi-join), then the
expression would be pushed past JOIN, and put it into a PROJECT below JOIN. And
JOIN would simply refer the projected expression in the join condition. (Such
logic is in SqlToRelConverter.java).
The fix we did is simply to make the logic in SqlToRelConverter shared by both
SqlToRelConverter and PushFilterPastJoinRule, so that the compound expression
in either "ON" clause or "WHERE" clause would be handled properly.
> Non-ansi join should not be processed as a filter on top of "on (true)" join
> ----------------------------------------------------------------------------
>
> Key: CALCITE-457
> URL: https://issues.apache.org/jira/browse/CALCITE-457
> Project: Calcite
> Issue Type: Bug
> Affects Versions: 0.9.1-incubating
> Reporter: Vladimir Sitnikov
> Assignee: Julian Hyde
>
> I've tested two plans and it turns out the query with non-ansi joins has
> extremely bad plan (note {{EnumerableJoinRel(condition=\[true\]}}):
> {code:sql}
> explain plan for select d."deptno", e."empid"
> from "hr"."emps" as e
> , "hr"."depts" as d
> where e."deptno" = d."deptno"+0
> PLAN=EnumerableCalcRel(expr#0..2=[{inputs}], expr#3=[CAST($t1):INTEGER NOT
> NULL], expr#4=[0], expr#5=[+($t2, $t4)], expr#6=[=($t3, $t5)], deptno=[$t2],
> empid=[$t0], $condition=[$t6])
> EnumerableJoinRel(condition=[true], joinType=[inner])
> EnumerableCalcRel(expr#0..4=[{inputs}], proj#0..1=[{exprs}])
> EnumerableTableAccessRel(table=[[hr, emps]])
> EnumerableCalcRel(expr#0..2=[{inputs}], deptno=[$t0])
> EnumerableTableAccessRel(table=[[hr, depts]])
> {code}
> Same works fine with ANSI style:
> {code:sql}
> explain plan for select d."deptno", e."empid"
> from "hr"."emps" as e
> join "hr"."depts" as d
> on (e."deptno" = d."deptno"+0)
> PLAN=EnumerableCalcRel(expr#0..3=[{inputs}], deptno=[$t2], empid=[$t0])
> EnumerableJoinRel(condition=[=($1, $3)], joinType=[inner])
> EnumerableCalcRel(expr#0..4=[{inputs}], expr#5=[CAST($t1):INTEGER NOT
> NULL], empid=[$t0], $f5=[$t5])
> EnumerableTableAccessRel(table=[[hr, emps]])
> EnumerableCalcRel(expr#0..2=[{inputs}], expr#3=[0], expr#4=[+($t0, $t3)],
> deptno=[$t0], $f3=[$t4])
> EnumerableTableAccessRel(table=[[hr, depts]])
> {code}
> The query that does not use calculations works fine even with non-ansi style:
> {code:sql}
> explain plan for select d."deptno", e."empid"
> from "hr"."emps" as e
> , "hr"."depts" as d
> where e."deptno" = d."deptno"
> PLAN=EnumerableCalcRel(expr#0..2=[{inputs}], deptno=[$t2], empid=[$t0])
> EnumerableJoinRel(condition=[=($1, $2)], joinType=[inner])
> EnumerableCalcRel(expr#0..4=[{inputs}], proj#0..1=[{exprs}])
> EnumerableTableAccessRel(table=[[hr, emps]])
> EnumerableCalcRel(expr#0..2=[{inputs}], deptno=[$t0])
> EnumerableTableAccessRel(table=[[hr, depts]])
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
