Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.11.0
    • Component/s: core
    • Labels:

      Description

      Following query generates sub-optimal plan

       explain plan for select * from scott.emp where deptno not in (select deptno from scott.dept where deptno = 20); 

      Following is the plan

      EnumerableCalc(expr#0..11=[{inputs}], expr#12=[0], expr#13=[=($t8, $t12)], expr#14=[false], expr#15=[IS NOT NULL($t11)], expr#16=[true], expr#17=[IS NULL($t7)], expr#18=[null], expr#19=[<($t9, $t8)], expr#20=[CASE($t13, $t14, $t15, $t16, $t17, $t18, $t19, $t16, $t14)], expr#21=[NOT($t20)], proj#0..7=[{exprs}], $condition=[$t21])
        EnumerableJoin(condition=[=($7, $10)], joinType=[left])
          EnumerableCalc(expr#0..9=[{inputs}], EMPNO=[$t2], ENAME=[$t3], JOB=[$t4], MGR=[$t5], HIREDATE=[$t6], SAL=[$t7], COMM=[$t8], DEPTNO=[$t9], c=[$t0], ck=[$t1])
            EnumerableJoin(condition=[true], joinType=[inner])
              JdbcToEnumerableConverter
                JdbcAggregate(group=[{}], c=[COUNT()], ck=[COUNT($0)])
                  JdbcFilter(condition=[=(CAST($0):INTEGER NOT NULL, 20)])
                    JdbcTableScan(table=[[SCOTT, DEPT]])
              JdbcToEnumerableConverter
                JdbcTableScan(table=[[SCOTT, EMP]])
          JdbcToEnumerableConverter
            JdbcAggregate(group=[{0, 1}])
              JdbcProject(DEPTNO=[$0], i=[true])
                JdbcFilter(condition=[=(CAST($0):INTEGER NOT NULL, 20)])
                  JdbcTableScan(table=[[SCOTT, DEPT]])
      

      As Julian pointed out in discussion on mailing list instead of two scans for DEPT one is sufficient as clearly DEPTNO is never null.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                julianhyde Julian Hyde
                Reporter:
                vgarg Vineet Garg
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: