[
https://issues.apache.org/jira/browse/CALCITE-2986?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16812867#comment-16812867
]
Vineet Garg commented on CALCITE-2986:
--------------------------------------
[~hyuan] I have given it some thought and as you noticed <>ANY is not
equivalent to NOT IN.
Problem with semi join transformation is that it will evaluate to only TRUE or
FALSE (SJ with have rows where ON condition is true and will not have rows
where ON condition is not true). But <>ANY requires it to be evaluated to
TRUE, FALSE or UNKNOWN.
<>ANY should probably be transformed in same way as IN is transformed in
SELECT. (e.g. select p_partkey, ( p_partkey IN (select p_partkey from part))
from part)
> Wrong results with =ANY subquery
> --------------------------------
>
> Key: CALCITE-2986
> URL: https://issues.apache.org/jira/browse/CALCITE-2986
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Vineet Garg
> Assignee: Vineet Garg
> Priority: Major
> Labels: sub-query
>
> ANY/SOME subqueries are rewritten using MAX/MIN and cross-join. This is wrong
> transformation for {{=ANY}} and {{<>ANY}} (and therefore {{=ALL}} and
> {{<>ALL}}).
> Query
> {code:sql}
> select * from "scott".emp where empno = any (select empno from "scott".emp);
> {code}
> Expected output for above query is all rows from {{scott.emp}} but actual is
> only one row
> Test case: e.g.
> https://github.com/apache/calcite/compare/master...vineetgarg02:CALCITE-2986
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
