pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPotential bug in postgres 8.2.4
9+ messages / 5 participants
[nested] [flat]
* Potential bug in postgres 8.2.4
@ 2007-05-24 11:20 Tomas Doran <bobtfish@bobtfish.net>
2007-05-24 11:34 ` Re: Potential bug in postgres 8.2.4 Marcin Stępnicki <mstepnicki@gmail.com>
2007-05-24 11:51 ` Re: Potential bug in postgres 8.2.4 Peter Eisentraut <peter_e@gmx.net>
0 siblings, 2 replies; 9+ messages in thread
From: Tomas Doran @ 2007-05-24 11:20 UTC (permalink / raw)
To: pgsql-sql; +Cc: tdoran@venda.com
I'm not sure if this is a bug, or not - but it looks like one to me.
if you say:
CREATE TABLE testtable (
col1 char(1),
data text
);
INSERT INTO testtable (col1, data) VALUES ('1', 'foobar');
INSERT INTO testtable (col1, data) VALUES ('2', 'foobarbaz');
The following queries all work:
INSERT INTO testtable (col1, data) VALUES (3::int, 'foobarbazquux');
SELECT * FROM testtable WHERE col1 = 3::int;
SELECT * FROM testtable WHERE col1 IN (1);
SELECT * FROM testtable WHERE col1 IN (1::int);
However these querys fail on 8.2.4, but work correctly on 8.1:
SELECT * FROM testtable WHERE col1 IN (1::int, 2::int);
SELECT * FROM testtable WHERE col1 IN (1, 2);
I could understand if the behavior was the same for single element IN
clauses, and multiple element IN clauses - however as their behavior
is different, and it used to work in 8.1....
Cheers
Tom
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Potential bug in postgres 8.2.4
2007-05-24 11:20 Potential bug in postgres 8.2.4 Tomas Doran <bobtfish@bobtfish.net>
@ 2007-05-24 11:34 ` Marcin Stępnicki <mstepnicki@gmail.com>
2007-05-24 11:54 ` Re: Potential bug in postgres 8.2.4 Tomas Doran <bobtfish@bobtfish.net>
1 sibling, 1 reply; 9+ messages in thread
From: Marcin Stępnicki @ 2007-05-24 11:34 UTC (permalink / raw)
To: pgsql-sql
Dnia Thu, 24 May 2007 12:20:54 +0100, Tomas Doran napisał(a):
> CREATE TABLE testtable (
> col1 char(1),
> data text
> );
>
> INSERT INTO testtable (col1, data) VALUES ('1', 'foobar'); INSERT INTO
> testtable (col1, data) VALUES ('2', 'foobarbaz');
>
> The following queries all work:
> INSERT INTO testtable (col1, data) VALUES (3::int, 'foobarbazquux');
> SELECT * FROM testtable WHERE col1 = 3::int; SELECT * FROM testtable WHERE
> col1 IN (1); SELECT * FROM testtable WHERE col1 IN (1::int);
>
> However these querys fail on 8.2.4, but work correctly on 8.1: SELECT *
> FROM testtable WHERE col1 IN (1::int, 2::int); SELECT * FROM testtable
> WHERE col1 IN (1, 2);
>
> I could understand if the behavior was the same for single element IN
> clauses, and multiple element IN clauses - however as their behavior is
> different, and it used to work in 8.1....
I'm not sure if I understand you correctly, but it seems that you are
comparing apples to oranges here (integer and character values). 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.
--
| And Do What You Will be the challenge | http://apcoln.linuxpl.org
| So be it in love that harms none | http://biznes.linux.pl
| For this is the only commandment. | http://www.juanperon.info
`---* JID: Aragorn_Vime@jabber.org *---' http://www.naszedzieci.org
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Potential bug in postgres 8.2.4
2007-05-24 11:20 Potential bug in postgres 8.2.4 Tomas Doran <bobtfish@bobtfish.net>
2007-05-24 11:34 ` Re: Potential bug in postgres 8.2.4 Marcin Stępnicki <mstepnicki@gmail.com>
@ 2007-05-24 11:54 ` Tomas Doran <bobtfish@bobtfish.net>
2007-05-24 12:19 ` Re: Potential bug in postgres 8.2.4 Richard Huxton <dev@archonet.com>
2007-05-24 12:30 ` Re: Potential bug in postgres 8.2.4 Marcin Stępnicki <mstepnicki@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Tomas Doran @ 2007-05-24 11:54 UTC (permalink / raw)
To: Marcin Stępnicki <mstepnicki@gmail.com>; +Cc: pgsql-sql
On 24 May 2007, at 12:34, Marcin Stępnicki wrote:
> Dnia Thu, 24 May 2007 12:20:54 +0100, Tomas Doran napisał(a):
>
>> CREATE TABLE testtable (
>> col1 char(1),
>> data text
>> );
>>
>> INSERT INTO testtable (col1, data) VALUES ('1', 'foobar'); INSERT
>> INTO
>> testtable (col1, data) VALUES ('2', 'foobarbaz');
>>
>> The following queries all work:
>> INSERT INTO testtable (col1, data) VALUES (3::int, 'foobarbazquux');
>> SELECT * FROM testtable WHERE col1 = 3::int; SELECT * FROM
>> testtable WHERE
>> col1 IN (1); SELECT * FROM testtable WHERE col1 IN (1::int);
>>
>> However these querys fail on 8.2.4, but work correctly on 8.1:
>> SELECT *
>> FROM testtable WHERE col1 IN (1::int, 2::int); SELECT * FROM
>> testtable
>> WHERE col1 IN (1, 2);
>>
>> I could understand if the behavior was the same for single element IN
>> clauses, and multiple element IN clauses - however as their
>> behavior is
>> different, and it used to work in 8.1....
>
> 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..
> 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.
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?
Cheers
Tom
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Potential bug in postgres 8.2.4
2007-05-24 11:20 Potential bug in postgres 8.2.4 Tomas Doran <bobtfish@bobtfish.net>
2007-05-24 11:34 ` Re: Potential bug in postgres 8.2.4 Marcin Stępnicki <mstepnicki@gmail.com>
2007-05-24 11:54 ` Re: Potential bug in postgres 8.2.4 Tomas Doran <bobtfish@bobtfish.net>
@ 2007-05-24 12:19 ` Richard Huxton <dev@archonet.com>
2007-05-24 12:34 ` Re: Potential bug in postgres 8.2.4 Tomas Doran <bobtfish@bobtfish.net>
1 sibling, 1 reply; 9+ messages in thread
From: Richard Huxton @ 2007-05-24 12:19 UTC (permalink / raw)
To: Tomas Doran <bobtfish@bobtfish.net>; +Cc: Marcin Stępnicki <mstepnicki@gmail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Potential bug in postgres 8.2.4
2007-05-24 11:20 Potential bug in postgres 8.2.4 Tomas Doran <bobtfish@bobtfish.net>
2007-05-24 11:34 ` Re: Potential bug in postgres 8.2.4 Marcin Stępnicki <mstepnicki@gmail.com>
2007-05-24 11:54 ` Re: Potential bug in postgres 8.2.4 Tomas Doran <bobtfish@bobtfish.net>
2007-05-24 12:19 ` Re: Potential bug in postgres 8.2.4 Richard Huxton <dev@archonet.com>
@ 2007-05-24 12:34 ` Tomas Doran <bobtfish@bobtfish.net>
2007-05-24 14:51 ` Re: Potential bug in postgres 8.2.4 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Tomas Doran @ 2007-05-24 12:34 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Marcin Stępnicki <mstepnicki@gmail.com>; 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Potential bug in postgres 8.2.4
2007-05-24 11:20 Potential bug in postgres 8.2.4 Tomas Doran <bobtfish@bobtfish.net>
2007-05-24 11:34 ` Re: Potential bug in postgres 8.2.4 Marcin Stępnicki <mstepnicki@gmail.com>
2007-05-24 11:54 ` Re: Potential bug in postgres 8.2.4 Tomas Doran <bobtfish@bobtfish.net>
2007-05-24 12:19 ` Re: Potential bug in postgres 8.2.4 Richard Huxton <dev@archonet.com>
2007-05-24 12:34 ` Re: Potential bug in postgres 8.2.4 Tomas Doran <bobtfish@bobtfish.net>
@ 2007-05-24 14:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-05-24 15:19 ` Re: Potential bug in postgres 8.2.4 Tomas Doran <bobtfish@bobtfish.net>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2007-05-24 14:51 UTC (permalink / raw)
To: Tomas Doran <bobtfish@bobtfish.net>; +Cc: Richard Huxton <dev@archonet.com>; Marcin Stępnicki <mstepnicki@gmail.com>; pgsql-sql
Tomas Doran <bobtfish@bobtfish.net> writes:
> 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)...
The direction of the future is that *all* those queries are going to
fail, because they're relying on an implicit integer-to-text conversion,
and its days are numbered. That might happen as soon as 8.3:
http://archives.postgresql.org/pgsql-hackers/2007-04/msg00017.php
but it's been on the radar screen for a very long time, eg
http://archives.postgresql.org/pgsql-bugs/2001-10/msg00108.php
http://archives.postgresql.org/pgsql-hackers/2002-04/msg00450.php
http://archives.postgresql.org/pgsql-sql/2004-01/msg00064.php
http://archives.postgresql.org/pgsql-hackers/2005-11/msg00510.php
I think the reason this particular behavior changed in 8.2 is the
re-implementation of multi-element IN tests as ScalarArrayOps;
but it's part of an intentional long-term tightening of SQL semantics,
and you're not going to get far with a proposal to revert it.
Fix your code.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Potential bug in postgres 8.2.4
2007-05-24 11:20 Potential bug in postgres 8.2.4 Tomas Doran <bobtfish@bobtfish.net>
2007-05-24 11:34 ` Re: Potential bug in postgres 8.2.4 Marcin Stępnicki <mstepnicki@gmail.com>
2007-05-24 11:54 ` Re: Potential bug in postgres 8.2.4 Tomas Doran <bobtfish@bobtfish.net>
2007-05-24 12:19 ` Re: Potential bug in postgres 8.2.4 Richard Huxton <dev@archonet.com>
2007-05-24 12:34 ` Re: Potential bug in postgres 8.2.4 Tomas Doran <bobtfish@bobtfish.net>
2007-05-24 14:51 ` Re: Potential bug in postgres 8.2.4 Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-05-24 15:19 ` Tomas Doran <bobtfish@bobtfish.net>
0 siblings, 0 replies; 9+ messages in thread
From: Tomas Doran @ 2007-05-24 15:19 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Richard Huxton <dev@archonet.com>; Marcin Stępnicki <mstepnicki@gmail.com>; pgsql-sql
On 24 May 2007, at 15:51, Tom Lane wrote:
> Tomas Doran <bobtfish@bobtfish.net> writes:
>> 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)...
>
> The direction of the future is that *all* those queries are going to
> fail, because they're relying on an implicit integer-to-text
> conversion,
> and its days are numbered. That might happen as soon as 8.3:
<snip>
That's no bad thing.
> I think the reason this particular behavior changed in 8.2 is the
> re-implementation of multi-element IN tests as ScalarArrayOps;
> but it's part of an intentional long-term tightening of SQL semantics,
> and you're not going to get far with a proposal to revert it.
I wasn't suggesting reverting it - just that lists of one element
being treated differently to lists of >1 element is not what I
expected :)
> Fix your code.
Easier said than done, but thankfully also not strictly my problem.
We have found that a newer database driver version does 'the right
thing' for us by quoting the values in the IN () list.
Our reason for not upgrading is that this driver connects using the
v8 protocol, and ergo logs an error when connecting to our legacy
postgres 7.2 databases (the error is logged in the DB backend as it
doesn't understand the v8 protocol). Yes, we are a million years
behind in upgrading - it's underway currently...
The number of machines using the db / making connections causes the
volumes of errors seen in the server logs to go totally mental, so we
can't use the new driver with the legacy DBs
The current plan is to rebuild our 7.2 server to just remove this
error message, and upgrade the database driver - as that wins us a
lot of other things too.
Thanks for the swift and comprehensive response guys!
Cheers
Tom
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Potential bug in postgres 8.2.4
2007-05-24 11:20 Potential bug in postgres 8.2.4 Tomas Doran <bobtfish@bobtfish.net>
2007-05-24 11:34 ` Re: Potential bug in postgres 8.2.4 Marcin Stępnicki <mstepnicki@gmail.com>
2007-05-24 11:54 ` Re: Potential bug in postgres 8.2.4 Tomas Doran <bobtfish@bobtfish.net>
@ 2007-05-24 12:30 ` Marcin Stępnicki <mstepnicki@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Marcin Stępnicki @ 2007-05-24 12:30 UTC (permalink / raw)
To: pgsql-sql
Dnia Thu, 24 May 2007 12:54:48 +0100, Tomas Doran napisał(a):
> If I can do something to make it work in the postgres backend, then that'd
> be acceptable, and I'm investigating that..
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Potential bug in postgres 8.2.4
2007-05-24 11:20 Potential bug in postgres 8.2.4 Tomas Doran <bobtfish@bobtfish.net>
@ 2007-05-24 11:51 ` Peter Eisentraut <peter_e@gmx.net>
1 sibling, 0 replies; 9+ messages in thread
From: Peter Eisentraut @ 2007-05-24 11:51 UTC (permalink / raw)
To: pgsql-sql; +Cc: Tomas Doran <bobtfish@bobtfish.net>; tdoran@venda.com
Am Donnerstag, 24. Mai 2007 13:20 schrieb Tomas Doran:
> CREATE TABLE testtable (
> col1 char(1),
> data text
> );
> The following queries all work:
> INSERT INTO testtable (col1, data) VALUES (3::int, 'foobarbazquux');
> SELECT * FROM testtable WHERE col1 = 3::int;
> SELECT * FROM testtable WHERE col1 IN (1);
> SELECT * FROM testtable WHERE col1 IN (1::int);
> However these querys fail on 8.2.4, but work correctly on 8.1:
> SELECT * FROM testtable WHERE col1 IN (1::int, 2::int);
> SELECT * FROM testtable WHERE col1 IN (1, 2);
All of this is strictly speaking incorrect anyway. And the queries that do
work will most likely start not working in a future version. All of this is
a gradual effort to reduce excessive automatic type casting.
I suggest you fix your application.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2007-05-24 15:19 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-24 11:20 Potential bug in postgres 8.2.4 Tomas Doran <bobtfish@bobtfish.net>
2007-05-24 11:34 ` Marcin Stępnicki <mstepnicki@gmail.com>
2007-05-24 11:54 ` Tomas Doran <bobtfish@bobtfish.net>
2007-05-24 12:19 ` Richard Huxton <dev@archonet.com>
2007-05-24 12:34 ` Tomas Doran <bobtfish@bobtfish.net>
2007-05-24 14:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-05-24 15:19 ` Tomas Doran <bobtfish@bobtfish.net>
2007-05-24 12:30 ` Marcin Stępnicki <mstepnicki@gmail.com>
2007-05-24 11:51 ` Peter Eisentraut <peter_e@gmx.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox