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

["Vladimir Sitnikov (JIRA)" <ji...@apache.org>]

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

Vladimir Sitnikov edited comment on CALCITE-457 at 11/10/14 7:47 PM:
---------------------------------------------------------------------

I investigated the issue and my conclusion is as follows:
1) {{PushProjectPastJoinRule.INSTANCE}} is *not* enabled by default
2) if I just add the rule it does not make the plans better. The rule does fire, however its sanity checks fail right after the start of {{onMatch}}.

I believe the bug is in the definition of the {{INSTANCE}}:
{code:java}
  public static final PushProjectPastJoinRule INSTANCE =
      new PushProjectPastJoinRule(PushProjector.ExprCondition.FALSE);
{code}

If I change {{FALSE}} to {{TRUE}}, then the plans become good.
I tried some variations and they work fine (from a plan perspective):
e.deptno = d.deptno+0
e.deptno = d.deptno+d.deptno
e.deptno + e.empid = d.deptno
e.deptno = d.deptno+d.deptno and e.deptno + e.empid = d.deptno
e.deptno = d.deptno+d.deptno and e.deptno + e.empid = d.deptno and e.empid + d.deptno = 0  <-- here the latest condition is not pushed.

I suggest the following:
1) Enable the rule by default
2) Rewrite {{if (pushProject.locateAllRefs()) return;}} part to {{matches}}
3) Add some tests.

Do you think it is the right approach here?



was (Author: vladimirsitnikov):
I investigated the issue and my conclusion is as follows:
1) {{PushProjectPastJoinRule.INSTANCE}} is *not* enabled by default
2) if I just add the rule it does not make the plans better. The rule does fire, however its sanity checks.

I believe the bug is in the definition of the {{INSTANCE}}:
{code:java}
  public static final PushProjectPastJoinRule INSTANCE =
      new PushProjectPastJoinRule(PushProjector.ExprCondition.FALSE);
{code}

If I change {{FALSE}} to {{TRUE}}, then the plans become good.
I tried some variations and they work fine (from a plan perspective):
e.deptno = d.deptno+0
e.deptno = d.deptno+d.deptno
e.deptno + e.empid = d.deptno
e.deptno = d.deptno+d.deptno and e.deptno + e.empid = d.deptno
e.deptno = d.deptno+d.deptno and e.deptno + e.empid = d.deptno and e.empid + d.deptno = 0  <-- here the latest condition is not pushed.

I suggest the following:
1) Enable the rule by default
2) Rewrite {{if (pushProject.locateAllRefs()) return;}} part to {{matches}}
3) Add some tests.

Do you think it is the right approach here?


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