[jira] [Updated] (CALCITE-2874) NOT IN correlated sub-query has unnecessary join

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

     [ https://issues.apache.org/jira/browse/CALCITE-2874?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Julian Hyde updated CALCITE-2874:
---------------------------------
    Summary: NOT IN correlated sub-query has unnecessary join  (was: NOT IN correlated subquery has un-necessary join)

> NOT IN correlated sub-query has unnecessary join
> ------------------------------------------------
>
>                 Key: CALCITE-2874
>                 URL: https://issues.apache.org/jira/browse/CALCITE-2874
>             Project: Calcite
>          Issue Type: Improvement
>            Reporter: Vineet Garg
>            Assignee: Vineet Garg
>            Priority: Major
>              Labels: sub-query
>
> Query:
> {code:sql}
> select count(*) as c
> from "scott".emp as e
> where sal + 100 not in (
>   select deptno
>   from dept
>   where dname = e.ename);
> {code}
> Plan:
> {code}
> EnumerableAggregate(group=[{}], C=[COUNT()])
>   EnumerableCalc(expr#0..9=[{inputs}], expr#10=[IS NOT NULL($t7)], expr#11=[<($t5, $t4)], expr#12=[OR($t10, $t11)], expr#13=[IS NOT TRUE($t12)], expr#14=[0], expr#15=[=($t4, $t14)], expr#16=[IS TRUE($t15)], expr#17=[IS NULL($t2)], expr#18=[OR($t13, $t16, $t17)], proj#0..9=[{exprs}], $condition=[$t18])
>     EnumerableJoin(condition=[AND(=($1, $8), =($2, $9))], joinType=[left])
>       EnumerableCalc(expr#0..5=[{inputs}], expr#6=[IS NOT NULL($t2)], expr#7=[0], expr#8=[=($t4, $t7)], expr#9=[IS TRUE($t8)], expr#10=[OR($t6, $t9)], proj#0..5=[{exprs}], $condition=[$t10])
>         EnumerableJoin(condition=[=($1, $3)], joinType=[left])
>           EnumerableCalc(expr#0..7=[{inputs}], proj#0..1=[{exprs}], SAL=[$t5])
>             EnumerableTableScan(table=[[scott, EMP]])
>           EnumerableAggregate(group=[{1}], c=[COUNT()], ck=[COUNT($0)])
>             EnumerableCalc(expr#0..2=[{inputs}], expr#3=[IS NOT NULL($t1)], proj#0..2=[{exprs}], $condition=[$t3])
>               EnumerableTableScan(table=[[scott, DEPT]])
>       EnumerableCalc(expr#0..4=[{inputs}], DEPTNO=[$t2], i=[$t3], DNAME=[$t4], SAL=[$t0])
>         EnumerableJoin(condition=[=($1, $2)], joinType=[inner])
>           EnumerableCalc(expr#0=[{inputs}], expr#1=[100], expr#2=[+($t0, $t1)], SAL=[$t0], $f1=[$t2])
>             EnumerableAggregate(group=[{5}])
>               EnumerableTableScan(table=[[scott, EMP]])
>           EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[IS NOT NULL($t1)], DEPTNO=[$t0], i=[$t3], DNAME=[$t1], $condition=[$t4])
>             EnumerableTableScan(table=[[scott, DEPT]])
> {code}
>  Bottom inner join b/w EMP and DEPT could be removed (only DEPT scan + project is required) and top join can have sal+100 = dept.deptno condition.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)