pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSevere SUBSELECT bug in 6.5 CVS
2+ messages / 1 participants
[nested] [flat]
* Severe SUBSELECT bug in 6.5 CVS
@ 1999-06-25 14:58 Chris Bitmead <chris.bitmead@bigfoot.com>
1999-06-26 15:29 ` Re: [HACKERS] Severe SUBSELECT bug in 6.5 CVS Chris Bitmead <chris.bitmead@bigfoot.com>
0 siblings, 1 reply; 2+ messages in thread
From: Chris Bitmead @ 1999-06-25 14:58 UTC (permalink / raw)
To: pgsql-hackers@hub.org
Using the very latest CVS I'm encountering a bug in SUBSELECTs.
This query returns 22 rows...
SELECT id,title FROM question;
This query returns 15 rows...
SELECT id,title FROM question WHERE question.id IN (SELECT webobject
FROM comment);
Therefore I would expect this query to return 7 rows. Instead it returns
none....
SELECT id,title FROM question WHERE question.id NOT IN (SELECT webobject
FROM comment);
I've tried it with a newly dumped and created database and it still
happens. However I can't get it to happen on a new and empty database
with simple data. In other words I can't come up with a simple test
case. All I can think to do therefore is put my data in a file for
download. It is 100k.
ftp://ftp.tech.com.au/pub/datadump.gz
As an aside, while creating this dump I tried to load it into another
database and drop a few tables to make it smaller. I then tried to dump
it again and got the following error...
dumpRules(): SELECT failed for table productv. Explanation from
backend: 'ERROR: cache lookup of attribute 1 in relation 1864370 failed
--
Chris Bitmead
mailto:chris@tech.com.au
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [HACKERS] Severe SUBSELECT bug in 6.5 CVS
1999-06-25 14:58 Severe SUBSELECT bug in 6.5 CVS Chris Bitmead <chris.bitmead@bigfoot.com>
@ 1999-06-26 15:29 ` Chris Bitmead <chris.bitmead@bigfoot.com>
0 siblings, 0 replies; 2+ messages in thread
From: Chris Bitmead @ 1999-06-26 15:29 UTC (permalink / raw)
To: pgsql-hackers@hub.org
Ok, I've come up with a test case now. It's got to do with nulls so I
don't know if someone's going to come back and say that this is the way
it's meant to work. It sure doesn't seem intuitive but perhaps someone
can tell me if it's correct behaviour. I feel sure it can't be because
it means one spurious record in the database could destroy lots of
previously working queries. In other words you could have a whole lot of
queries that work. Then if some joker puts a record in the database with
a null, all the other records will no longer be returned. Anyway, here
is the simple test case...
httpd=> create table a (i int, aa text);
CREATE
httpd=> create table b (i int, bb text);
CREATE
httpd=> insert into a values(1, 'foo');
INSERT 1878534 1
httpd=> insert into b values(null, 'bar');
INSERT 1878535 1
httpd=> select * from a where i not in (select i from b);
i|aa
-+--
(0 rows)
I would expect the single record in a to be returned here. Imagine I
have thousands of records in the database that this query returns. Then
someone adds a record to b with a null. Now all those previous return
values will no longer be returned. Seems really dangerous but maybe
that is how nulls work???
Chris Bitmead wrote:
>
> Using the very latest CVS I'm encountering a bug in SUBSELECTs.
> This query returns 22 rows...
>
> SELECT id,title FROM question;
>
> This query returns 15 rows...
>
> SELECT id,title FROM question WHERE question.id IN (SELECT webobject
> FROM comment);
>
> Therefore I would expect this query to return 7 rows. Instead it returns
> none....
>
> SELECT id,title FROM question WHERE question.id NOT IN (SELECT webobject
> FROM comment);
>
> I've tried it with a newly dumped and created database and it still
> happens. However I can't get it to happen on a new and empty database
> with simple data. In other words I can't come up with a simple test
> case. All I can think to do therefore is put my data in a file for
> download. It is 100k.
>
> ftp://ftp.tech.com.au/pub/datadump.gz
>
> As an aside, while creating this dump I tried to load it into another
> database and drop a few tables to make it smaller. I then tried to dump
> it again and got the following error...
>
> dumpRules(): SELECT failed for table productv. Explanation from
> backend: 'ERROR: cache lookup of attribute 1 in relation 1864370 failed
>
> --
> Chris Bitmead
> mailto:chris@tech.com.au
--
Chris Bitmead
mailto:chris@tech.com.au
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~1999-06-26 15:29 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-06-25 14:58 Severe SUBSELECT bug in 6.5 CVS Chris Bitmead <chris.bitmead@bigfoot.com>
1999-06-26 15:29 ` Chris Bitmead <chris.bitmead@bigfoot.com>
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