IN does not negate, exists does - Mailing list pgsql-bugs

From Achilleus Mantzios
Subject IN does not negate, exists does
Date
Msg-id Pine.LNX.4.44.0402051814260.19827-100000@matrix.gatewaynet.com
Whole thread Raw
In response to Returned due to virus; was:Status  (pgsql-bugs@postgresql.org)
Responses Re: IN does not negate, exists does
List pgsql-bugs
EXISTS and IN seem to work ok for:

dynacom=# SELECT count(*) from parts p where p.id in (select md.partid
from machdefs md);
 count
-------
 12656
(1 row)

dynacom=# SELECT count(*) from parts p where exists (select 1 from
machdefs md where md.partid = p.id);
 count
-------
 12656
(1 row)

Whereas IN seems to not working ok when negated as shown below:

dynacom=# SELECT count(*) from parts p where p.id not in (select md.partid
from machdefs md);
 count
-------
     0
(1 row)

dynacom=# SELECT count(*) from parts p where not exists (select 1 from
machdefs md where md.partid = p.id);
 count
-------
   291
(1 row)

dynacom=# SELECT version();
                                                  version
-----------------------------------------------------------------------------------------------------------
 PostgreSQL 7.4.1 on i386-unknown-freebsd5.1, compiled by GCC gcc (GCC)
3.2.2 [FreeBSD] 20030205 (release)
(1 row)


On PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC 2.96, i get
identical results.

Is this a known issue? Addressed before?

--
-Achilleus

pgsql-bugs by date:

Previous
From: pgsql-bugs@postgresql.org
Date:
Subject: Returned due to virus; was:Status
Next
From: Tom Lane
Date:
Subject: Re: IN does not negate, exists does