[jira] [Comment Edited] (CALCITE-1483) Suboptimal plan for NOT IN query

["Julian Hyde (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-1483?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15681955#comment-15681955 ] 

Julian Hyde edited comment on CALCITE-1483 at 11/20/16 10:48 PM:
-----------------------------------------------------------------

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


was (Author: julianhyde):
Fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/a4bdab68.

> Suboptimal plan for NOT IN query
> --------------------------------
>
>                 Key: CALCITE-1483
>                 URL: https://issues.apache.org/jira/browse/CALCITE-1483
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>            Reporter: Vineet Garg
>            Assignee: Julian Hyde
>             Fix For: 1.11.0
>
>
> Following query generates sub-optimal plan
> {code} explain plan for select * from scott.emp where deptno not in (select deptno from scott.dept where deptno = 20); {code}
> Following is the plan
> {code}
> 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]])
> {code}
> As Julian pointed out in discussion on mailing list instead of two scans for DEPT one is sufficient as clearly DEPTNO is never null.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)