Re: Why does =ANY(<array>) 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.