In-Reply-To: <465582E1.3030409@archonet.com>
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>
	<465582E1.3030409@archonet.com>
Mime-Version: 1.0 (Apple Message framework v752.2)
Content-Type: text/plain; charset=UTF-8; delsp=yes; format=flowed
Message-Id: <AFCB5437-EFE9-43A6-A455-A2B761E86581@bobtfish.net>
Cc: =?UTF-8?Q?Marcin_St=C4=99pnicki?= <mstepnicki@gmail.com>,
	pgsql-sql@postgresql.org
Content-Transfer-Encoding: quoted-printable
From: Tomas Doran <bobtfish@bobtfish.net>
Subject: Re: Potential bug in postgres 8.2.4
Date: Thu, 24 May 2007 13:34:10 +0100
To: Richard Huxton <dev@archonet.com>


On 24 May 2007, at 13:19, Richard Huxton wrote:

> Tomas Doran wrote:
>> On 24 May 2007, at 12:34, Marcin St=C4=99pnicki wrote:
>
>>> I'm not sure if I understand you correctly, but it seems that you =20=

>>> 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 =20
>> hysterical raisins..
>> In the short term, adding the appropriate cast (in our code) isn't =20=

>> an option...
>> If I can do something to make it work in the postgres backend, =20
>> 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 =20
> application.

That would be a great idea, however we have several live clients who =20
have been upgraded (with entire QA and customer QA phases of testing) =20=

before we found this. So we're now stuffed :)

>> Yes, indeed - however I think it's a bug as 'SELECT * FROM =20
>> testtable WHERE col1 IN (1)' DOES work, but 'SELECT * FROM =20
>> testtable WHERE col1 IN (1, 2)' does NOT work..
>> This is, at the very least, is a glaring inconsistency around how =20
>> IN clauses are handled in different situations.
>
> What's biting you is the overly-loose matching against a single =20
> item (or all in 8.1). Most of the problems with PG seem to be where =20=

> checks weren't strict enough in a previous version.

The tightening in general is biting me, but if the answer was 'it was =20=

deliberate tightening', and the behavior was consistent, then we'd =20
have just dealt with it - it's the in-consistent behavior that makes =20
me think this is a bug (or at least a gotcha, as it's not what you =20
expect)...

>
>> If this was a deliberate tightning of the behavior, is there a =20
>> changelog entry/link to come docs about when this change happened =20
>> that anyone can point me to?
>
> My guess is that 8.2 is planning this by converting your IN into an =20=

> array and testing against that. Actually, I can test that:

That was my guess too - but I'm having a bad day and haven't got any =20
further in playing with it than posted, thanks.

I'll be looking through the source / changelogs this afternoon and =20
work out when/why this started happening.

> EXPLAIN ANALYSE SELECT * FROM foo WHERE a IN (1::char,2::char);
>                                           QUERY PLAN
> ----------------------------------------------------------------------=20=

> -------------------------
>  Seq Scan on foo  (cost=3D0.00..36.12 rows=3D21 width=3D5) (actual =20
> time=3D0.029..0.033 rows=3D2 loops=3D1)
>    Filter: (a =3D 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 =20
> cast - only solution would be to hack the ANY code I suspect.

Our DB driver does the right thing with quoting the values for us if =20
we use a later version than the one we're running. This may be the =20
solution we take..

The idea of hacking in the ANY code and then running the server in =20
our production environment scares me ;)

Cheers
Tom