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

Suboptimal plan for NOT IN query

VotersWatch issueWatchersLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • None
    • 1.11.0
    • core

    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

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

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

            Dates

              Created:
              Updated:
              Resolved:

              Slack

                Issue deployment