Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-6786

ANY/SOME operator yields multiple rows in correlated queries

    XMLWordPrintableJSON

Details

    Description

      Currently, the following query yields 2 output rows:

      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;
      

      Result:

      +------+
      | TEST |
      +------+
      |      |
      | true |
      +------+
      2 rows selected
      

      According to any's syntax, the result should be:

      +------+
      | TEST |
      +------+
      | true |
      +------+
      1 row selected
      

      As a reference, DuckDB returns the correct value.

      The following query returns the correct value though (note that it's not correlated):

      SELECT TRUE = ANY (SELECT * FROM (VALUES (TRUE), (NULL)) as x(a)) AS test;
      

      I'm currently working on a fix, but it seems that this order by/limit clause should also be applied to correlated queries.

      Thoughts?

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              racevedoo Rafael Acevedo
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: