[jira] [Commented] (CALCITE-457) Non-ansi join should not be processed as a filter on top of "on (true)" join

["Jinfeng Ni (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-457?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14578012#comment-14578012 ] 

Jinfeng Ni commented on CALCITE-457:
------------------------------------

Hi Julian,

Submitted a new PR for CALCITE-457. See https://github.com/apache/incubator-calcite/pull/92

It uses a different approach to fix the issue of non-ansi join case : try to leverage the existing code logic for ansi-join case, and call that logic in FilterJoinRule to handle non-ansi join case.

Please review the patch and let me know if there is any problem. 

Thanks!
 



> 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)