[
https://issues.apache.org/jira/browse/CALCITE-5160?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Dmitry Sysolyatin updated CALCITE-5160:
---------------------------------------
Summary: ANY/SOME, ALL operators should support collection expressions
(was: ANY/SOME, ALL operators should support collection expressions)
> 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
> Time Spent: 10m
> 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)
