[
https://issues.apache.org/jira/browse/CALCITE-5160?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Dmitry Sysolyatin updated CALCITE-5160:
---------------------------------------
Description:
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]
was:
At the moment ANY, SOME functions support subquery:
{code}
SELECT 1 = SOME (SELECT * FROM UNNEST(ARRAY[1,2,3]))
{code}
But if input argument is array, then query fails
{code}
SELECT 1 = SOME (ARRAY[1,2,3])
SELECT 1 = SOME(<table>.<array_type_field>) FROM <table>
{code}
[https://www.postgresql.org/docs/current/functions-comparisons.html#id-1.5.8.30.16]
It is useful for checking if a value exists in an array.
> 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
> Reporter: Dmitry Sysolyatin
> Assignee: Dmitry Sysolyatin
> Priority: Major
> Labels: pull-request-available
>
> 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)
