Postgres does not limit which operators can be used with ANY and ALL

[freshtonic]

In PR #963 a check was introduced which limits which operators can be used with ANY and ALL expressions.

Postgres can parse more (possibly all binary operators, investigation pending) in this location. Postgres only seems to care that the operator yields a boolean - which is a semantic error, not a syntax (parse) error.

Example (semantic error, not a parse error):

select 123 % ANY(array[246]);
ERROR:  op ANY/ALL (array) requires operator to yield boolean
LINE 1: select 123 % ANY(array[246]);
                   ^

The following code in src/parser/mod.rs:2893-2908 is where the allowlist of operators is enforced:

if !matches!(
    op,
    BinaryOperator::Gt
        | BinaryOperator::Lt
        | BinaryOperator::GtEq
        | BinaryOperator::LtEq
        | BinaryOperator::Eq
        | BinaryOperator::NotEq
) {
    return parser_err!(
        format!(
        "Expected one of [=, >, <, =>, =<, !=] as comparison operator, found: {op}"
    ),
        tok.span.start
    );
};

I propose that instead of hard-coding the allowed operators we instead check if the dialect is Postgres, and if so allow arbitrary BinaryOperators to be used. Existing behaviour will be preserved for all other dialects.

This is a blocker for a new customer at my day job - I will do the work myself - so really I'm looking for feedback on the suggested approach.

[freshtonic]

Now with PR: #1842

[mvzink]

Note that there are some other things Postgres supports for ANY/ALL which our current parsing approach doesn't; e.g. it treats LIKE as a valid operator in that context, but we have a separate AST variant for it instead of treating LIKE as a binary operator. See #1770.