duan xiong created CALCITE-4765:
-----------------------------------
Summary: When complex correlated EXISTS sub-query as a SCALAR
subquery, Calcite return wrong result.
Key: CALCITE-4765
URL: https://issues.apache.org/jira/browse/CALCITE-4765
Project: Calcite
Issue Type: Bug
Affects Versions: 1.27.0
Reporter: duan xiong
Assignee: duan xiong
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.3.4#803005)
