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

Dawid Kuroczko wrote:

Versions 8.0, 8.1 work fine, while version 8.2.5 (and earlier probably too):

postgres=> CREATE TEMP TABLE test (id varchar(32));
CREATE TABLE
postgres=> INSERT INTO test SELECT * FROM generate_series(1,100);
INSERT 0 100
postgres=> SELECT * FROM test WHERE id IN (50);
id
----
50
(1 row)

postgres=> SELECT * FROM test WHERE id IN (55,50);
ERROR: IN types character varying and integer cannot be matched

This works fine in version 8.0 and 8.1.

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.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

"Heikki Linnakangas" <heikki@enterprisedb.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
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.

regards, tom lane

On 10/19/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:

"Heikki Linnakangas" <heikki@enterprisedb.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.

"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.

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.

regards, tom lane

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)