[
https://issues.apache.org/jira/browse/CALCITE-4765?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zhen Chen resolved CALCITE-4765.
--------------------------------
Fix Version/s: 1.42.0
Assignee: Zhen Chen (was: xiong duan)
Resolution: Duplicate
Added test for this Jira via
[{{15d79db}}|https://github.com/apache/calcite/commit/15d79dbbbbaee7ec283c4293baf6cb3c94664232]
Thanks for review [~nobigo] [~mbudiu] [~dongsl]
There is currently no corresponding fix for this issue, but after review by
multiple people, it is believed that the problem no longer exists.
> Complex correlated EXISTS sub-queries used as scalar subqueries can return
> wrong results
> ----------------------------------------------------------------------------------------
>
> Key: CALCITE-4765
> URL: https://issues.apache.org/jira/browse/CALCITE-4765
> Project: Calcite
> Issue Type: Bug
> Affects Versions: 1.27.0
> Reporter: xiong duan
> Assignee: Zhen Chen
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.42.0
>
>
> The SQL1:
> {code:java}
> select * from emps e1 where EXISTS (
> select * from (
> select e2.deptno
> from emps e2
> where e2.commission = e1.commission
> ) as table3 where table3.deptno<>e1.deptno)
> {code}
> return:
>
> {code:java}
> +-----+------+----------+------+----------+
> |empid|deptno|name |salary|commission|
> +-----+------+----------+------+----------+
> |110 |10 |Theodore |11500 |250 |
> |170 |30 |Theodore |11500 |250 |
> +-----+------+----------+------+----------+
> {code}
>
> Then the SQL2:
> {code:java}
> SELECT * ,EXISTS (select * from (SELECT e2.deptno
> FROM emps e2
> where e1.commission = e2.commission) as table3 where
> table3.deptno<>e1.deptno) from emps e1{code}
> will return:
> {code:java}
> +-----+------+----------+------+----------+------+
> |empid|deptno|name |salary|commission|exists|
> +-----+------+----------+------+----------+------+
> |100 |10 |Bill |10000 |1000 |false |
> |200 |20 |Eric |8000 |500 |false |
> |150 |10 |Sebastian |7000 |NULL |false |
> |110 |10 |Theodore |11500 |250 |true |
> |170 |30 |Theodore |11500 |250 |true |
> |140 |10 |Sebastian |7000 |NULL |false |
> +-----+------+----------+------+----------+------+
> {code}
> But in calcite, The Sql2 will throw information:
> Correlation variable $cor0 should be defined.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
