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