Обсуждение: PostgreSQL 8.2 Bug in casting varchar to int in SELECT ... WHERE IN ( ... )

VmVyc2lvbnMgOC4wLCA4LjEgd29yayBmaW5lLCB3aGlsZSB2ZXJzaW9uIDgu
Mi41IChhbmQgZWFybGllciBwcm9iYWJseSB0b28pOgoKcG9zdGdyZXM9PiBD
UkVBVEUgVEVNUCBUQUJMRSB0ZXN0IChpZCB2YXJjaGFyKDMyKSk7CkNSRUFU
RSBUQUJMRQpwb3N0Z3Jlcz0+IElOU0VSVCBJTlRPIHRlc3QgU0VMRUNUICog
RlJPTSBnZW5lcmF0ZV9zZXJpZXMoMSwxMDApOwpJTlNFUlQgMCAxMDAKcG9z
dGdyZXM9PiBTRUxFQ1QgKiBGUk9NIHRlc3QgV0hFUkUgaWQgSU4gKDUwKTsK
IGlkCi0tLS0KIDUwCigxIHJvdykKCnBvc3RncmVzPT4gU0VMRUNUICogRlJP
TSB0ZXN0IFdIRVJFIGlkIElOICg1NSw1MCk7CkVSUk9SOiAgSU4gdHlwZXMg
Y2hhcmFjdGVyIHZhcnlpbmcgYW5kIGludGVnZXIgY2Fubm90IGJlIG1hdGNo
ZWQKClRoaXMgd29ya3MgZmluZSBpbiB2ZXJzaW9uIDguMCBhbmQgOC4xLgoK
VGhlIGJ1ZyB3YXMgZm91bmQgYnkgTWljaGGzIFdvamFzLgoKICBSZWdhcmRz
LAogICAgIERhd2lkIEt1cm9jemtvCg==

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)