From: Tomas Doran <bobtfish@bobtfish.net>
To: Marcin Stępnicki <mstepnicki@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Potential bug in postgres 8.2.4
Date: Thu, 24 May 2007 12:54:48 +0100
Message-ID: <72573535-0946-45DE-B5E4-E57236965B0E@bobtfish.net> (raw)
In-Reply-To: <pan.2007.05.24.11.34.03.780983@gmail.com>
References: <3D2864D1-BF64-4609-B307-1C70A4FE503F@bobtfish.net>
<pan.2007.05.24.11.34.03.780983@gmail.com>
On 24 May 2007, at 12:34, Marcin Stępnicki wrote:
> Dnia Thu, 24 May 2007 12:20:54 +0100, Tomas Doran napisał(a):
>
>> CREATE TABLE testtable (
>> col1 char(1),
>> data text
>> );
>>
>> INSERT INTO testtable (col1, data) VALUES ('1', 'foobar'); INSERT
>> INTO
>> testtable (col1, data) VALUES ('2', 'foobarbaz');
>>
>> The following queries all work:
>> INSERT INTO testtable (col1, data) VALUES (3::int, 'foobarbazquux');
>> SELECT * FROM testtable WHERE col1 = 3::int; SELECT * FROM
>> testtable WHERE
>> col1 IN (1); SELECT * FROM testtable WHERE col1 IN (1::int);
>>
>> However these querys fail on 8.2.4, but work correctly on 8.1:
>> SELECT *
>> FROM testtable WHERE col1 IN (1::int, 2::int); SELECT * FROM
>> testtable
>> WHERE col1 IN (1, 2);
>>
>> I could understand if the behavior was the same for single element IN
>> clauses, and multiple element IN clauses - however as their
>> behavior is
>> different, and it used to work in 8.1....
>
> I'm not sure if I understand you correctly, but it seems that you are
> comparing apples to oranges here (integer and character values).
Yep, totally - it's not nice, but we need to do it at $ork for
hysterical raisins..
In the short term, adding the appropriate cast (in our code) isn't an
option...
If I can do something to make it work in the postgres backend, then
that'd be acceptable, and I'm investigating that..
> I am a
> big fan of weakly typed languages like Python myself, but this
> situation
> is different. I'd say that PostgreSQL 8.1 did a cast somewhere
> "behind the
> scenes" but personally I think it is a bad idea. Consider:
>
> SELECT * FROM testtable WHERE col1::int IN (1, 2);
>
> instead.
Yes, indeed - however I think it's a bug as 'SELECT * FROM testtable
WHERE col1 IN (1)' DOES work, but 'SELECT * FROM testtable WHERE col1
IN (1, 2)' does NOT work..
This is, at the very least, is a glaring inconsistency around how IN
clauses are handled in different situations.
If this was a deliberate tightning of the behavior, is there a
changelog entry/link to come docs about when this change happened
that anyone can point me to?
Cheers
Tom
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bobtfish@bobtfish.net, mstepnicki@gmail.com
Subject: Re: Potential bug in postgres 8.2.4
In-Reply-To: <72573535-0946-45DE-B5E4-E57236965B0E@bobtfish.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox