Re: Why does =ANY() need an extra cast when used

On Mon, 23 Aug 2004, Frank van Vugt wrote:

> > > works =# select 1 = ANY ('{1,2,3}'::int[]);
> > > doesn't work =# select 1 = ANY (select '{1,2,3}'::int[]);
> > > works =# select 1 = ANY ((select '{1,2,3}'::int[])::int[]);
>
> I may be misinterpreting your reply but.....
>
> My real-world application has a set-returning PL/pgSQL function for which I
> created a type, so the function is returning rows of this type. One of the
> fields in this type is an array of int.
>
> > The second query looks to me to be of the form = ANY (table
> > subquery) which already had defined behavior by spec.
>
> Yes, what I want is to be able to do something like:
>
> select some_fields
> from some_table
> where some_int = ANY(
>     select field_of_type_array_of_int
>     from plpgsql_method_returning_custom_type
>     where we_just_return_a_single_record);
>
> But this won't work, so I'm not quite getting what you mean by 'which already
> had defined behavior by spec'

SQL92/99 basically defines
 A = ANY (table subquery) to mean
  For each row returned by the subquery, compare A to the column using the
   = operator

We defined on top of that something like
 A = ANY (array expression) to mean
  For each element in the array compare A to the array element using the =
   operator.

If we made, A = ANY (select arraycol ...) to mean the latter, queries that
 were using it as the former would change meaning from their already
 defined SQL behavior. Perhaps if you wanted to define it as <non array
 type> = ANY (select arraycol ...) it might be okay, but right now
 changing that would mean that you couldn't do
  select arraycol = ANY(select arraycol from table)

I think your third query (with the cast) would be the "correct" way to
indicate the intent. That is effectively
A = ANY (CAST(scalar subquery AS array type)).