= ANY (SELECT ..) and type casts, what's going on here?

Russell Smith <mr-russ@pws.com.au> writes:

Is anybody able to explain the following behaviour?

mr-russ=# SELECT 'BU'::varchar = ANY ((select '{BU,CI}'::varchar[]));
ERROR: operator does not exist: character varying = character varying[]
LINE 1: SELECT 'BU'::varchar = ANY ((select '{BU,CI}'::varchar[]));
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
mr-russ=# SELECT 'BU'::varchar = ANY ((select '{BU,CI}'::varchar[])::varchar[]);
?column?
----------
t
(1 row)

For ANY (or ALL) with a sub-select, the sub-select is expected to return
rows, and the left-hand value is compared to each row's contained value.
This is required behavior per SQL standard.

If the right-hand argument of ANY/ALL is *not* a sub-select, then it's
expected to be an expression yielding an array value, and the left-hand
value is compared to each array element. AFAIR, this is not in the SQL
standard but is a Postgres extension.

In your second example, the RHS is a cast expression, not directly a
sub-select, so it behaves as per the second rule. The sub-select
embedded within it doesn't count.

There isn't any provision for ANY/ALL with a sub-select returning a
series of array values; that would require iteration in "two
dimensions", and we don't do that. It would be contrary to spec in any
case, I think, and would break existing use cases where the ANY/ALL
operator is one that takes a scalar on the left and an array on the
right.

regards, tom lane