[
https://issues.apache.org/jira/browse/CALCITE-2986?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16817092#comment-16817092
]
Vineet Garg commented on CALCITE-2986:
--------------------------------------
bq. It's not true that IN will be rewritten to LEFT join, actually it will
rewrite to a INNER join[1], the left is a table scan and the right is an agg.
[~danny0405] I meant IN within SELECT. IN is rewritten into INNER where logic
is TRUE (i.e. IN in WHERE) but for IN in SELECT where logic could be TRUE/FALSE
or TRUE/FALSE/UNKNOWN it is transformed into LEFT OUTER JOIN
e.g.
{code:sql}
select sal,
empno IN (
select deptno from dept
where dept.name <> 'Engineering')
from emp
{code}
{code}
LogicalProject(SAL=[$5], EXPR$1=[IS NOT NULL($10)])
LogicalJoin(condition=[=($0, $9)], joinType=[left])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalProject(DEPTNO=[$0], i=[true])
LogicalFilter(condition=[<>(CAST($1):VARCHAR(11) NOT NULL,
'Engineering')])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
{code}
bq. For <> ANY, i think it can also be interpreted as a
NestedLoopJoin(physical) and INNER join for logical.
I am not sure how this will work. Can you provide few examples? What would be
the transformation for <>ANY in WHERE and <>ANY in SELECT?
> 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)
