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?