Re: PostgreSQL 8.2 Bug in casting varchar to int in SELECT ... WHERE IN ( ... )

On 10/20/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Dawid Kuroczko" <qnex42@gmail.com> writes:
> > What troubles me here is that surprise factor is unusally high here.
> > While I understand mechanics why IN (1) works while IN (1,2) does not,
> > I think random developers are going to be confused.
>
> If you're not testing against 8.3 then this argument doesn't carry much
> weight.  8.3 will reject *both* cases in the examples you've mentioned.

Fair enough.  I have checked that both cases are rejected in 8.3 beta1

> > PS: I wonder why explicitly using IN (ARRAY[...]) works.
>
> Um, it does not work AFAICS:
>
> regression=# select 'foo'::varchar in (array[1,2,3]);
> ERROR:  operator does not exist: character varying = integer[]
> LINE 1: select 'foo'::varchar in (array[1,2,3]);
>                               ^
> HINT:  No operator matches the given name and argument type(s). You may need to add explicit type casts.

A thinko on my side, what I inteded to write was, that explicit = ANY
(ARRAY[...]) works
fine under 8.2.5 while IN (...) does not.

postgres=> SELECT 'foo'::varchar = ANY (array[1,2,3]), version();
 ?column? |                                        version
----------+----------------------------------------------------------------------------------------
 f        | PostgreSQL 8.2.5 on i486-pc-linux-gnu, compiled by GCC cc
(GCC) 4.2.1 (Debian 4.2.1-5)
(1 row)

postgres=> SELECT 'foo'::varchar = ANY (array[1,2,3]);
 ?column?
----------
 f
(1 row)