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

On 10/19/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Heikki Linnakangas" <heikki(at)enterprisedb(dot)com> writes:
> > Note that neither SELECT will work on 8.3, because we no longer have an
> > implicit cast from integer to text. I suppose the above is an oversight
> > in how we handle IN-clauses (starting with 8.2 an IN-clause is
> > transformed into an "x = ANY (...)" expression), but I don't think it's
> > worth changing.
>
> Yeah, the reason for the difference in behavior is that when there's
> just one IN-list item, the parser reduces the thing to a plain "x = y"
> expression, which succeeds in the same cases where writing it out that
> way would work. If there's more than one item then it wants to find a
> common data type for all the expressions involved. The implementation
> details have changed (repeatedly) over time, but given that we're moving

I thought so. Interestingly if you do transformation into ANY "manually",
you get a work around. ;-)

postgres=> SELECT * FROM test WHERE id IN (5,10);
ERROR: IN types character varying and integer cannot be matched
postgres=> SELECT * FROM test WHERE id = ANY (ARRAY[5,10]);
id
----
5
10
(2 rows)
postgres=> EXPLAIN SELECT * FROM test WHERE id = ANY (ARRAY[5,10]);
QUERY PLAN
--------------------------------------------------------------
Seq Scan on test (cost=0.00..13.26 rows=59 width=34)
Filter: ((id)::text = ANY (('{5,10}'::integer[])::text[]))

> to stricter behavior for implicit casting, I don't think there's really
> anything to fix here.
>
> You need to either cast the varchar to int, or quote the list items to
> make them look like varchars, depending on which comparison semantics
> you're really after.

Well, what I'm after is helping port application from another RDBMS,
and this is one problem developers stumbled upon.

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. I think it would
be better from surprise-factor point of view if <text> IN (<int>) would
also cause error.

Regards,
Dawid

PS: I wonder why explicitly using IN (ARRAY[...]) works.