NOT IN queries

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

In response to

Responses

Browse pgsql-announce by date

  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

Browse pgsql-general by date

  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

Browse pgsql-hackers by date

  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