From: | Nic Ferrier <nferrier(at)tapsellferrier(dot)co(dot)uk> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | NOT IN queries |
Date: | 2002-04-01 15:55:32 |
Message-ID: | 87vgbbo1l7.fsf@pooh-sticks-bridge.tapsellferrier.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-announce pgsql-general pgsql-hackers |
The following seems to be a bug in 7.2 (and in 7.1.2) I'm pretty sure
it worked before, certainly it's something I do a lot (but postgresql
isn't the only database I use).
The bug concerns a NOT IN on a list generated by a select. If you
have two tables thus:
create table t1 (id integer, name varchar(20), t2_id integer);
insert into t1 (id, name, t2_id) values (1, 'nic', 2);
insert into t1 (id, name, t2_id) values (2, 'jim', NULL);
create table t2 (id integer, name varchar(20));
insert into t1 (id, name, t2_id) values (1, 'ferrier');
insert into t1 (id, name, t2_id) values (2, 'broadbent');
And now do this query:
select * from t2 where id not in (select t2_id from t1);
then I get a NULL response (ie: no rows returned).
What I SHOULD get is the row from t2 with id == 2;
Nic Ferrier
From | Date | Subject | |
---|---|---|---|
Next Message | Doug McNaught | 2002-04-01 16:14:52 | Re: NOT IN queries |
Previous Message | David Wheeler | 2002-03-17 20:34:49 | ANNOUNCE: Bricolage 1.2.3 |
From | Date | Subject | |
---|---|---|---|
Next Message | Doug McNaught | 2002-04-01 16:14:52 | Re: NOT IN queries |
Previous Message | Fran Fabrizio | 2002-04-01 15:49:16 | PostgreSQL success stories |
From | Date | Subject | |
---|---|---|---|
Next Message | Jan Wieck | 2002-04-01 16:14:10 | Re: Data integrity and sanity check |
Previous Message | Jessica Perry Hekman | 2002-04-01 15:49:04 | Re: timeout implementation issues |