[jira] [Comment Edited] (CALCITE-5160) ANY/SOME, ALL operators should support collection expressions

["Dmitry Sysolyatin (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-5160?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17704583#comment-17704583 ] 

Dmitry Sysolyatin edited comment on CALCITE-5160 at 3/24/23 12:09 PM:
----------------------------------------------------------------------

Fixed in https://github.com/apache/calcite/commit/ce3d1333b909099565f18a0a22bbe64cd5e33c67; Thanks for review [~bchapuis] and [~FrankZou]


was (Author: dmsysolyatin):
Fixed in https://github.com/apache/calcite/commit/ce3d1333b909099565f18a0a22bbe64cd5e33c67; Thanks for review [~bchapuis][~FrankZou]

> ANY/SOME, ALL operators should support collection expressions
> -------------------------------------------------------------
>
>                 Key: CALCITE-5160
>                 URL: https://issues.apache.org/jira/browse/CALCITE-5160
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 1.34.0
>            Reporter: Dmitry Sysolyatin
>            Assignee: Dmitry Sysolyatin
>            Priority: Major
>              Labels: pull-request-available
>             Fix For: 1.35.0
>
>          Time Spent: 2h 40m
>  Remaining Estimate: 0h
>
> At the moment ANY, SOME functions support subquery:
> {code:java}
> SELECT 1 = SOME (SELECT * FROM UNNEST(ARRAY[1,2,3]))
> {code}
> But if input argument is array, then query fails
> {code:java}
> SELECT 1 = SOME (ARRAY[1,2,3])
> SELECT 1 = SOME(<table>.<array_type_field>) FROM <table>
> {code}
>  
> Specification for ANY/SOME [1]:
> {code:java}
> expression binary_comparison_operator ANY (collection expression)
> expression binary_comparison_operator SOME (collection expression)
> {code}
> 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]:
> {code:java}
> expression binary_comparison_operator ALL (collection expression)
> {code}
> 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]



--
This message was sent by Atlassian Jira
(v8.20.10#820010)