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.

        Issue Links

          Activity

          Hide
          vgarg Vineet Garg added a comment -

          Quoting Julian "Note that there are two scans of DEPT, but one is sufficient because DEPTNO is never null. In the JdbcAggregate, c always equals ck, and therefore the CASE can be simplified, and therefore the scan of DEPT that produces c and ck can be dropped, but Calcite rules cannot deduce that fact."

          I'll try to find more examples of such queries

          Show
          vgarg Vineet Garg added a comment - Quoting Julian "Note that there are two scans of DEPT, but one is sufficient because DEPTNO is never null. In the JdbcAggregate, c always equals ck, and therefore the CASE can be simplified, and therefore the scan of DEPT that produces c and ck can be dropped, but Calcite rules cannot deduce that fact." I'll try to find more examples of such queries
          Hide
          julianhyde Julian Hyde added a comment -

          I've started work in https://github.com/julianhyde/calcite/tree/1483-not-in-not-null.

          Vineet Garg, I need some more examples to test this on before I will commit. Can you provide them?

          Show
          julianhyde Julian Hyde added a comment - I've started work in https://github.com/julianhyde/calcite/tree/1483-not-in-not-null . Vineet Garg , I need some more examples to test this on before I will commit. Can you provide them?
          Hide
          vgarg Vineet Garg added a comment -

          Julian Hyde Following are few more examples where inner query is guaranteed to return non-null values

           select * from scott.emp where deptno not in (select deptno  from scott.dept where deptno IS NOT NULL); 
           select * from scott.emp where deptno not in (select count(*)  from scott.dept); 
           select * from scott.emp where deptno not in (select 1+1  from scott.dept); 
           select * from scott.emp where deptno not in (select deptno from scott.dept sd where sd.deptno IN (select distinct deptno from scott.dept)); 

          I'm trying to come up with more examples

          Show
          vgarg Vineet Garg added a comment - Julian Hyde Following are few more examples where inner query is guaranteed to return non-null values select * from scott.emp where deptno not in (select deptno from scott.dept where deptno IS NOT NULL); select * from scott.emp where deptno not in (select count(*) from scott.dept); select * from scott.emp where deptno not in (select 1+1 from scott.dept); select * from scott.emp where deptno not in (select deptno from scott.dept sd where sd.deptno IN (select distinct deptno from scott.dept)); I'm trying to come up with more examples
          Hide
          julianhyde Julian Hyde added a comment -

          Those examples were helpful. Thanks. I created tests, and even the "is not null" one turned up a bug, and I need to extend RexImplicationChecker. I think I have enough to submit this patch. Just need some more time to get it working.

          Show
          julianhyde Julian Hyde added a comment - Those examples were helpful. Thanks. I created tests, and even the "is not null" one turned up a bug, and I need to extend RexImplicationChecker . I think I have enough to submit this patch. Just need some more time to get it working.
          Hide
          julianhyde Julian Hyde added a comment - - edited

          Fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/b196b26d.

          (I edited this comment because I made a typo in an earlier commit comment. I had to force-push to correct it.)

          Show
          julianhyde Julian Hyde added a comment - - edited Fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/b196b26d . (I edited this comment because I made a typo in an earlier commit comment. I had to force-push to correct it.)
          Hide
          vgarg Vineet Garg added a comment -

          Julian Hyde This won't be applied if only SubqueryRemoveRule is called right ? i.e. we don't go through SqlToRelConverter.

          Show
          vgarg Vineet Garg added a comment - Julian Hyde This won't be applied if only SubqueryRemoveRule is called right ? i.e. we don't go through SqlToRelConverter.
          Hide
          julianhyde Julian Hyde added a comment -

          Yes, but I made RelMdPredicates smarter so that it can deduce that deptno is not null in more cases. That could potentially help people creating RelNode trees by paths other than SqlToRelConverter.

          Show
          julianhyde Julian Hyde added a comment - Yes, but I made RelMdPredicates smarter so that it can deduce that deptno is not null in more cases. That could potentially help people creating RelNode trees by paths other than SqlToRelConverter.
          Hide
          julianhyde Julian Hyde added a comment -

          Resolved in release 1.11.0 (2017-01-11).

          Show
          julianhyde Julian Hyde added a comment - Resolved in release 1.11.0 (2017-01-11).

            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:

                Development