You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Rafael Acevedo (Jira)" <ji...@apache.org> on 2025/01/14 20:35:00 UTC

[jira] [Created] (CALCITE-6786) ANY/SOME operator yields multiple rows in correlated queries

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)