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

Hi,

Is anybody able to explain the following behaviour?

Server is 8.4.7  RHEL5 build.  Also happens on 8.4.8 Ubuntu x64 package.

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)

mr-russ=#


What I don't understand is what happens to the single SELECT's type, is it because select returns a row?  The error
doesn'tseem to match what I would expect? 

Thanks

Russell

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