Vineet Garg created CALCITE-1546: ------------------------------------ Summary: Wrong result/plan for NOT IN subqueries 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
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)