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

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

> The following works :
>
> db=# select 1 = ANY ('{1,2,3}'::int[]);
>  ?column?
> ----------
>  t
> (1 row)
>
>
> This doesn't :
>
> db=# select 1 = ANY (select '{1,2,3}'::int[]);
> ERROR:  operator does not exist: integer = integer[]
> HINT:  No operator matches the given name and argument type(s). You may need
> to add explicit type casts.
>
> Using an extra case, the above can easily be made to work :
>
> db=# select 1 = ANY ((select '{1,2,3}'::int[])::int[]);
>  ?column?
> ----------
>  t
> (1 row)
>
>
> I'm just wondering why the array returned by the inner select is not casted by
> ANY() automagically?


Barring the cast syntax and such, the first and last query would I believe
be illegal in SQL92/99, so we defined useful behavior for them for this
case.  The second query looks to me to be of the form = ANY (table
subquery) which already had defined behavior by spec. Changing it to act
like the first or last would break that spec behavior.