[CALCITE-457] Push condition of non-ansi join into join operator

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 0.9.1-incubating
• Fix Version/s: 1.4.0-incubating
• Assignee: Julian Hyde
• Reporter: Vladimir Sitnikov
• Created: 06/Nov/14 19:47
• Updated: 01/Sep/15 03:02
• Resolved: 15/Jun/15 22:59

Description

I've tested two plans and it turns out the query with non-ansi joins has extremely bad plan (note EnumerableJoinRel(condition=[true]):

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

Same works fine with ANSI style:

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

The query that does not use calculations works fine even with non-ansi style:

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

Issue Links

• relates to: CALCITE-850 Remove push down expressions from FilterJoinRule and create a new rule for it