The ANY function here have different meanings, determined by its argument:

• [A] a condition if arg is a subquery or a parameter;
• [B] an aggregate if arg is a bool;
• [C] comparison/condition if arg is an array.

[A] is clear in H2, but [B] and [C] is hard to distinct in parsing because column type is unknown. In most databases, however, ANY is never recognized as an aggregate. If [C] is only supported in PostgreSQL (I don't know whether Oracle and DB2 also support ANY(array)), things can be simpler: just skip [B] and convert [C] to ARRAY_CONTAINS or new Comparison/ConditionAnyArray.

I may close this and submit another pr.

{<|>|<=|>=|=|<>} ANY(query) is a quantified comparison predicate.
{<|>|<=|>=|=|<>} ANY(non-query expression) is a simple comparison with aggregate function.
{<|>|<=|>=|=|<>} (ANY(anything, including subquery)) is a simple comparison with aggregate function.

if arg is a bool;

In H2 other data types may be used as boolean values.

All other constructions are PostgreSQL-specific. H2 supports only = ANY(?) for some limited compatibility with PostgreSQL, this specific construction is used often and was requested multiple times.

You can add {<|>|<=|>=|=|<>} ANY(array) too, but not in that way.

Actually this problem is really even more complicated than I thought initially, because we can't determine data type of expression early if it is not a constant expression. It looks like {<|>|<=|>=|=|<>} ANY(non-query expression) with non-constant expression should always be parsed not as required by the SQL Standard unconditionally, but I don't like “features” that work like a bugs. We need to design and discuss this feature first.

Superseded by #3822.