[
https://issues.apache.org/jira/browse/CALCITE-2986?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16818396#comment-16818396
]
Vineet Garg commented on CALCITE-2986:
--------------------------------------
bq. I think the <> ANY must be taken into the join condition, and the physical
join must be a NestedLoopJoin, each time the join left side take a loop look up
for the eight side, if left side found there is any right join keys <> the left
keys for the whole right table, it should returns early. Or the whole right
side must be probed.
This physical implementation will also have to keep track of the presence of
NULLs, if there is no match and there existed a NULL it has to return NULL from
left side. BUT if there was no NULL it has to return FALSE.
I suppose there you could add/tweak physical implementation to support <>ANY
but the problem at hand is how to transform it at logical level which is still
unclear to me.
> 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: pull-request-available, sub-query
> Time Spent: 1h 10m
> Remaining Estimate: 0h
>
> 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)
