From: Chris Bitmead <chris.bitmead@bigfoot.com>
To: pgsql-hackers@hub.org
Subject: Re: [HACKERS] Severe SUBSELECT bug in 6.5 CVS
Date: Sun, 27 Jun 1999 01:29:45 +1000
Message-ID: <3774F1E9.D2D8FE92@bigfoot.com> (raw)
References: <Pine.BSF.4.05.9906160647280.8888-100000@paprika.michvhf.com>
<3773990C.B58264C1@bigfoot.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: chris.bitmead@bigfoot.com, pgsql-hackers@hub.org
Subject: Re: [HACKERS] Severe SUBSELECT bug in 6.5 CVS
In-Reply-To: <3774F1E9.D2D8FE92@bigfoot.com>
References: <Pine.BSF.4.05.9906160647280.8888-100000@paprika.michvhf.com> <3773990C.B58264C1@bigfoot.com> <3774F1E9.D2D8FE92@bigfoot.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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