Message-ID: <465582E1.3030409@archonet.com>
Date: Thu, 24 May 2007 13:19:45 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.0 (X11/20070326)
MIME-Version: 1.0
To: Tomas Doran <bobtfish@bobtfish.net>
Cc: =?UTF-8?B?TWFyY2luIFN0xJlwbmlja2k=?= <mstepnicki@gmail.com>,
	pgsql-sql@postgresql.org
Subject: Re: Potential bug in postgres 8.2.4
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>
In-Reply-To: <72573535-0946-45DE-B5E4-E57236965B0E@bobtfish.net>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

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