Re: Potential bug in postgres 8.2.4

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.12rows=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