[jira] [Commented] (CALCITE-4889) Double join is created for NOT IN

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

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

Julian Hyde commented on CALCITE-4889:
--------------------------------------

There was an issue logged for this (IN-list) a while ago. Can someone find it?

Yes, I agree. A double Join seems excessive. "NOT IN subquery" is difficult to implement (especially if there are nulls on the left or right side) but "NOT IN list" is easier to analyze statically.

> Double join is created for NOT IN
> ---------------------------------
>
>                 Key: CALCITE-4889
>                 URL: https://issues.apache.org/jira/browse/CALCITE-4889
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 1.28.0
>            Reporter: Vladimir Sitnikov
>            Priority: Major
>
> The following queries yield several joins in the plan.
> I think double joins are excessive here, especially for the first case where all the values are non-nullable.
> {code}select * from "scott".emp where (empno, deptno) not in ((7369, 20), (7499, 30));{code}
> {noformat}
> select * from "scott".emp where empno not in (null, 7782);
> EnumerableCalc(expr#0..12=[{inputs}], expr#13=[0:BIGINT], expr#14=[=($t8, $t13)], expr#15=[IS NULL($t12)], expr#16=[>=($t9, $t8)], expr#17=[AND($t15, $t16)], expr#18=[OR($t14, $t17)], proj#0..7=[{exprs}], $condition=[$t18])
>   EnumerableMergeJoin(condition=[=($10, $11)], joinType=[left])
>     EnumerableSort(sort0=[$10], dir0=[ASC])
>       EnumerableCalc(expr#0..9=[{inputs}], proj#0..9=[{exprs}], EMPNO0=[$t0])
>         EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
>           EnumerableTableScan(table=[[scott, EMP]])
>           EnumerableAggregate(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($0)])
>             EnumerableValues(tuples=[[{ null }, { 7782 }]])
>     EnumerableSort(sort0=[$0], dir0=[ASC])
>       EnumerableCalc(expr#0=[{inputs}], expr#1=[true], proj#0..1=[{exprs}])
>         EnumerableValues(tuples=[[{ null }, { 7782 }]])
> {noformat}
> {noformat}
> select * from "scott".emp where (empno, deptno) not in ((1, 2), (3, null));
> EnumerableCalc(expr#0..14=[{inputs}], expr#15=[0:BIGINT], expr#16=[=($t8, $t15)], expr#17=[IS NULL($t14)], expr#18=[>=($t9, $t8)], expr#19=[IS NOT NULL($t11)], expr#20=[AND($t17, $t18, $t19)], expr#21=[OR($t16, $t20)], proj#0..7=[{exprs}], $condition=[$t21])
>   EnumerableMergeJoin(condition=[AND(=($10, $12), =($11, $13))], joinType=[left])
>     EnumerableSort(sort0=[$10], sort1=[$11], dir0=[ASC], dir1=[ASC])
>       EnumerableCalc(expr#0..9=[{inputs}], proj#0..9=[{exprs}], EMPNO0=[$t0], DEPTNO0=[$t7])
>         EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
>           EnumerableTableScan(table=[[scott, EMP]])
>           EnumerableAggregate(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($0, $1)])
>             EnumerableValues(tuples=[[{ 1, 2 }, { 3, null }]])
>     EnumerableSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC])
>       EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], proj#0..2=[{exprs}])
>         EnumerableValues(tuples=[[{ 1, 2 }, { 3, null }]])
> {noformat}



--
This message was sent by Atlassian Jira
(v8.20.1#820001)