Rafael Acevedo created CALCITE-6786:
---------------------------------------
Summary: ANY/SOME operator yields multiple rows in correlated
queries
Key: CALCITE-6786
URL: https://issues.apache.org/jira/browse/CALCITE-6786
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.38.0
Reporter: Rafael Acevedo
Currently, the following query yields 2 output rows:
{code:sql}
WITH tb as (select array(SELECT * FROM (VALUES (TRUE), (NULL)) as x(a)) as a)
SELECT TRUE = ANY (SELECT b
FROM UNNEST(a) AS x1(b)) AS test
FROM tb;
{code}
Result:
{code}
+------+
| TEST |
+------+
| |
| true |
+------+
2 rows selected
{code}
According to any's syntax, the result should be:
{code}
+------+
| TEST |
+------+
| true |
+------+
1 row selected
{code}
As a reference, DuckDB returns the correct value.
The following query returns the correct value though (note that it's not
correlated):
{code:sql}
SELECT TRUE = ANY (SELECT * FROM (VALUES (TRUE), (NULL)) as x(a)) AS test;
{code}
I'm currently working on a fix, but it seems that [this order
by/limit|https://github.com/apache/calcite/blob/575fc1a583b3f5b5febdee40da4c5cb46c9022b1/core/src/main/java/org/apache/calcite/rel/rules/SubQueryRemoveRule.java#L700-L705]
clause should also be applied to correlated queries.
Thoughts?
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
