Re: = ANY (SELECT ..) and type casts, what's going on here? - Mailing list pgsql-general

From Tom Lane
Subject Re: = ANY (SELECT ..) and type casts, what's going on here?
Date
Msg-id 10178.1308181364@sss.pgh.pa.us
Whole thread Raw
In response to = ANY (SELECT ..) and type casts, what's going on here?  (Russell Smith <mr-russ@pws.com.au>)
List pgsql-general
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

pgsql-general by date:

Previous
From: "David Johnston"
Date:
Subject: Re: { SELECT *->NOT(column1, column2) FROM table } syntax idea
Next
From: Craig Ringer
Date:
Subject: Symbols and versioning of binary releases; running a symbol server