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
