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
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
