[jira] [Updated] (CALCITE-1546) Wrong plan for NOT IN sub-queries with disjunction

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

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

Julian Hyde updated CALCITE-1546:
---------------------------------
    Summary: Wrong plan for NOT IN sub-queries with disjunction  (was: Wrong result/plan for NOT IN subqueries with disjunction)

> Wrong plan for NOT IN sub-queries with disjunction
> --------------------------------------------------
>
>                 Key: CALCITE-1546
>                 URL: https://issues.apache.org/jira/browse/CALCITE-1546
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>            Reporter: Vineet Garg
>            Assignee: Julian Hyde
>              Labels: sub-query
>             Fix For: 1.12.0
>
>
> Query:
> {code}
> select * from emp where sal = 4 OR empno NOT IN (select deptno from dept){code}
> Plan:
> {code}
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
>   LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
>     LogicalFilter(condition=[OR(=($5, 4), NOT(CASE(IS NOT NULL($10), true, false)))])
>       LogicalJoin(condition=[=($0, $9)], joinType=[left])
>         LogicalTableScan(table=[[CATALOG, SALES, EMP]])
>         LogicalAggregate(group=[{0, 1}])
>           LogicalProject(DEPTNO=[$0], i=[true])
>             LogicalProject(DEPTNO=[$0])
>               LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> There is no null check branch i.e. with count(\*), count(c) in the plan. This produces wrong results if deptno is null in dept.



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