ANY | SOME with array

[katzyn]

PostgreSQL supports non-standard comparison { ANY | SOME } (array).

There is also standard ANY | SOME aggregate function missing in PostgreSQL, but existing in H2. This aggregate function with array argument is meaningless, it should be used with boolean or some compatible argument instead, but in data type of non-constant expressions is not known in parser and in some cases it isn't known even on later stages of query preparation (if it is a parameter or session variable, for example). Anyway, on later stages it's too late to make a decision, aggregate functions convert query to a grouped query, but quantified comparison don't. Such late decision can be implemented with some hacks (see #2670 for example), but we don't want to have such code in H2 and maintain it.

We have a similar syntax conflict even with standard syntax, therefore H2 requires additional parentheses in some rarely used cases:

Note that if ANY or SOME aggregate function is placed on the right side of comparison operation and argument of this function is a subquery additional parentheses around aggregate function are required, otherwise it will be parsed as quantified comparison predicate.

Example:

ANY(NAME LIKE 'W%')
A = (ANY((SELECT B FROM T)))

For quantified comparison with array these parentheses should be required around all ANY and SOME aggregate functions placed on the right side of comparison regardless of type of their non-constant arguments. Such requirement is harder than our current one.

We need to make a decision about this feature. Do we want to have it and have some standard incompatibilities caused by it or we don't? Note that there is a standard UNNEST function and it can be used to convert array into query, but some people may need to use the syntax from PostgreSQL.

Hypothetically we can parse such construction only in PostgreSQL compatibility mode, but such deviation between different modes is also not the best thing to have.

@lukaseder
@grandinj
Maybe you have some opinions about this feature and problems caused by it?

[lukaseder]

I believe that the very reason PostgreSQL doesn't support ANY and SOME aggregate functions is the syntactic conflict they cause with quantified comparison predicates. The SQL standard has a few of these syntactic problems, where an ambiguity is non-trivial to resolve (or in some cases impossible). This is why PostgreSQL implements non-standard BOOL_AND() (EVERY()) and BOOL_OR() (ANY(), SOME()) aggregate functions instead. I find that a pragmatic choice, given that ANY() is hardly known or used, regardless if as a quantifier or an aggregate function.

I would personally prefer if the quantified comparison predicates were left untouched. They predated array types and the EVERY() / ANY() aggregate functions in the SQL standard.

[lukaseder]

See also this discussion on Twitter: https://twitter.com/lukaseder/status/1075127096815562753, and e.g. also this reply from Vik: https://twitter.com/pg_xocolatl/status/1075170531916292096

[katzyn]

This issue is not about standard quantified comparison predicates. The main question here is about non-standard PostgreSQL-style quantified comparison predicates with arrays. Currently H2 doesn't support them, with exception for = ANY(?) (with JDBC parameter). Hypothetically H2 can support them all (comparison ALL|ANY|SOME(array)), but such support significantly increases the area of syntax conflict between them and comparison operation with ANY|SOME aggregate function.

[lukaseder]

I see. Well, I personally find the quantifiers useful, but few people do. You can always sit this one out until actual users would like to see the functionality...