Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-457

Push condition of non-ansi join into join operator

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 0.9.1-incubating
    • Fix Version/s: 1.4.0-incubating
    • Component/s: None
    • Labels:
      None

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

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                julianhyde Julian Hyde
                Reporter:
                vladimirsitnikov Vladimir Sitnikov
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: