You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by "Vineet Garg (JIRA)" <ji...@apache.org> on 2016/12/20 06:29:58 UTC
[jira] [Created] (CALCITE-1546) Wrong result/plan for NOT IN
subqueries with disjunction
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)