[
https://issues.apache.org/jira/browse/CALCITE-1546?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15764931#comment-15764931
]
Julian Hyde commented on CALCITE-1546:
--------------------------------------
You're right that LogicVisitor should return UNKNOWN_AS_TRUE in this case. The
fix is to carefully rework LogicVisitor so that it returns the right result
based on the structure of the predicate. That is the point of LogicVisitor: to
follow the structure of the predicate, and in particular the flow of UNKNOWN
values and negations.
By the way, I think it's damaging to try to create "workarounds". You may have
noticed that we have been playing whack-a-mole for some time. If we are not
rigorous we will create wrong or inefficient plans for other queries. We need
to rigorously test each fix so that we are not continually taking two steps
forward, one step back (mea culpa for the regression I introduced in
CALCITE-1526).
> 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
> Labels: sub-query
>
> 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)
