You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Julian Hyde (JIRA)" <ji...@apache.org> on 2015/06/09 01:56:00 UTC
[jira] [Assigned] (CALCITE-457) Non-ansi join should not be
processed as a filter on top of "on (true)" join
[ https://issues.apache.org/jira/browse/CALCITE-457?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Julian Hyde reassigned CALCITE-457:
-----------------------------------
Assignee: Jinfeng Ni (was: Julian Hyde)
> 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: Jinfeng Ni
>
> 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)