[CALCITE-1546] Wrong plan for NOT IN sub-queries with disjunction

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.12.0
• Component/s: core
• Labels: sub-query
• Assignee: Julian Hyde
• Reporter: Vineet Garg
• Created: 20/Dec/16 06:29
• Updated: 24/Mar/17 03:20
• Resolved: 15/Jan/17 15:51

Description

Query:

select * from emp where sal = 4 OR empno NOT IN (select deptno from dept)

Plan:

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

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.