Thread: strange change (and error) in 8.3 ?

hi,
this query:
select 1 where '1'::text in (1::int8);

worked fine in 8.2:
# select version();
                                            version
------------------------------------------------------------------------------------------------
 PostgreSQL 8.2.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 (Ubuntu 4.1.2-0ubuntu4)
(1 row)

# select 1 where '1'::text in (1::int8);
 ?column?
----------
        1
(1 row)

but in 8.3 i get:
# select 1 where '1'::text in (1::int8);
ERROR:  operator does not exist: text = bigint
LINE 1: select 1 where '1'::text in (1::int8);
                                 ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.


why? i mean - i see that types are bad, but it worked in 8.2. why it was broken/modified in 8.3?

depesz

--
http://www.depesz.com/ - nowy, lepszy depesz

"hubert depesz lubaczewski" <depesz@gmail.com> writes:
> but in 8.3 i get:
> # select 1 where '1'::text in (1::int8);
> ERROR:  operator does not exist: text = bigint

> why? i mean - i see that types are bad, but it worked in 8.2. why it was
> broken/modified in 8.3?

This is intentional --- implicit casts to text are gone.  You should be
happy that the above now fails, because it's calling your attention to
the fact that you've got very ill-defined semantics there.  Is the
comparison going to be done according to text rules, or according to
int8 rules?  (This would matter, for instance, if there was a space
in the string.)  There are dozens of examples in the archives of people
having been burnt by the old behavior, for instance

http://archives.postgresql.org/pgsql-general/2007-02/msg01028.php
http://archives.postgresql.org/pgsql-general/2007-02/msg00871.php
http://archives.postgresql.org/pgsql-hackers/2005-11/msg00510.php

            regards, tom lane

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

thanks for clarification. actually it's not my app, i'm just a bystander which was asked about the issue :).

reasoning seems to be perfectly valid. i hope they will be happy with it as well :)

best regards,

depesz