Re: Potential bug in postgres 8.2.4 - Mailing list pgsql-sql

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

> 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.

That would be a great idea, however we have several live clients who
have been upgraded (with entire QA and customer QA phases of testing)
before we found this. So we're now stuffed :)

>> 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.

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

>
>> 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:

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

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

> 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.

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

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

Cheers
Tom