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

ANY/SOME, ALL operators should support collection expressions

Rank to TopRank to BottomBulk Copy AttachmentsBulk Move AttachmentsVotersWatch issueWatchersConvert to sub-taskLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    Description

      At the moment ANY, SOME functions support subquery:

      SELECT 1 = SOME (SELECT * FROM UNNEST(ARRAY[1,2,3]))
      

      But if input argument is array, then query fails

      SELECT 1 = SOME (ARRAY[1,2,3])
      SELECT 1 = SOME(<table>.<array_type_field>) FROM <table>
      

       

      Specification for ANY/SOME [1]:

      expression binary_comparison_operator ANY (collection expression)
      expression binary_comparison_operator SOME (collection expression)
      

      The right-hand side is a parenthesized expression, which must yield an collection value. The left-hand expression is evaluated and compared to each element of the collection using the given binary comparison operator. The result of ANY is “true” if any true result is obtained. The result is “false” if no true result is found.

      If the collection expression yields a null collection, the result of ANY will be null. If the left-hand expression yields null, the result of ANY is ordinarily null (though a non-strict comparison operator could possibly yield a different result). Also, if the right-hand collection contains any null elements and no true comparison result is obtained, the result of ANY will be null, not false (again, assuming a strict comparison operator). This is in accordance with SQL's normal rules for Boolean combinations of null values.

      SOME is a synonym for ANY.

       

      Specification for ALL [2]:

      expression binary_comparison_operator ALL (collection expression)
      

      The right-hand side is a parenthesized expression, which must yield an collection value. The left-hand expression is evaluated and compared to each element of the collection using the given binary comparison operator. The result of ALL is “true” if all comparisons yield true. The result is “false” if any false result is found.

      If the array expression yields a null collection, the result of ALL will be null. If the left-hand expression yields null, the result of ALL is ordinarily null (though a non-strict comparison operator could possibly yield a different result). Also, if the right-hand collection contains any null elements and no false comparison result is obtained, the result of ALL will be null, not true (again, assuming a strict comparison operator). This is in accordance with SQL's normal rules for Boolean combinations of null values.

       

       

      [1]  https://www.postgresql.org/docs/current/functions-comparisons.html#id-1.5.8.30.16
      [2] https://www.postgresql.org/docs/current/functions-comparisons.html#id-1.5.8.30.17

      Attachments

        Activity

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

            dmsysolyatin Dmitry Sysolyatin
            dmsysolyatin Dmitry Sysolyatin
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - 0h
                0h
                Logged:
                Time Spent - 2h 40m
                2h 40m

                Slack

                  Issue deployment