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

On 10/20/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Dawid Kuroczko" <qnex42(at)gmail(dot)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)