[jira] [Resolved] (CALCITE-457) Push condition of non-ansi join into join operator

["Julian Hyde (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CALCITE-457?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Julian Hyde resolved CALCITE-457.
---------------------------------
       Resolution: Fixed
    Fix Version/s: 1.4.0-incubating

Fixed in http://git-wip-us.apache.org/repos/asf/incubator-calcite/commit/f3cae130.

> Push condition of non-ansi join into join operator
> --------------------------------------------------
>
>                 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
>             Fix For: 1.4.0-incubating
>
>
> 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)