[
https://issues.apache.org/jira/browse/CALCITE-2986?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16818490#comment-16818490
]
Haisheng Yuan edited comment on CALCITE-2986 at 4/16/19 1:05 AM:
-----------------------------------------------------------------
{quote}
Problem with semi join transformation is that it will evaluate to only TRUE or
FALSE (SJ will output 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.
{quote}
I did't get it. Currently semijoin extends equijoin, that's the restriction.
But putting this aside, why can't <>ANY be transformed left semi join? Can you
give a concrete example?
was (Author: hyuan):
>> Problem with semi join transformation is that it will evaluate to only TRUE
>> or FALSE (SJ will output 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.
I did't get it. Currently semijoin extends equijoin, that's the restriction.
But putting this aside, why can't <>ANY be transformed left semi join? Can you
give a concrete example?
> 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)
