[
https://issues.apache.org/jira/browse/CALCITE-1546?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15763437#comment-15763437
]
Vineet Garg commented on CALCITE-1546:
--------------------------------------
Problem seems to be with *LogicVisitor.find* which is determining the logic for
NOT IN subquery to be *TRUE_FALSE* when it should be *UNKNOWN_AS_TRUE*.
*LogicVisitor.find* determines the logic of any subquery with OR predicate to
be *TRUE_FALSE* irrespective of IN/NOT IN/EXISTS/NOT EXISTS.
[~julianhyde] Can you take a look and suggest what could a possible fix be for
this? Calling *LogicVisitor.find* with *Logic.UNKNOWN_AS_FALSE* instead of with
*Logic.TRUE* in *SubQueryRemoveRule* works as a workaround but it produces very
inefficient plan and I have yet to test if this workaround works in for all
cases.
> 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)
