From: Richard Huxton <dev@archonet.com>
To: Tomas Doran <bobtfish@bobtfish.net>
Cc: 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 13:19:45 +0100
Message-ID: <465582E1.3030409@archonet.com> (raw)
In-Reply-To: <72573535-0946-45DE-B5E4-E57236965B0E@bobtfish.net>
References: <3D2864D1-BF64-4609-B307-1C70A4FE503F@bobtfish.net>
<pan.2007.05.24.11.34.03.780983@gmail.com>
<72573535-0946-45DE-B5E4-E57236965B0E@bobtfish.net>
Tomas Doran wrote:
>
> On 24 May 2007, at 12:34, Marcin Stępnicki wrote:
>> 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..
Well, if I were you, I'd just stick with 8.1 until you can fix the
application.
>> 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.
What's biting you is the overly-loose matching against a single item (or
all in 8.1). Most of the problems with PG seem to be where checks
weren't strict enough in a previous version.
> 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?
My guess is that 8.2 is planning this by converting your IN into an
array and testing against that. Actually, I can test that:
EXPLAIN ANALYSE SELECT * FROM foo WHERE a IN (1::char,2::char);
QUERY PLAN
-----------------------------------------------------------------------------------------------
Seq Scan on foo (cost=0.00..36.12 rows=21 width=5) (actual
time=0.029..0.033 rows=2 loops=1)
Filter: (a = ANY ('{1,2}'::bpchar[]))
Total runtime: 0.085 ms
(3 rows)
Yep. I don't think you can work round this by adding an implicit cast -
only solution would be to hack the ANY code I suspect.
--
Richard Huxton
Archonet Ltd
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: dev@archonet.com, bobtfish@bobtfish.net, mstepnicki@gmail.com
Subject: Re: Potential bug in postgres 8.2.4
In-Reply-To: <465582E1.3030409@archonet.com>
* 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