Skip site navigation (1) Skip section navigation (2)

IN does not negate, exists does

From: Achilleus Mantzios <achill(at)matrix(dot)gatewaynet(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: IN does not negate, exists does
Date: 2004-02-05 16:18:45
Message-ID: Pine.LNX.4.44.0402051814260.19827-100000@matrix.gatewaynet.com (view raw or flat)
Thread:
Lists: 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


In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2004-02-05 17:03:07
Subject: Re: IN does not negate, exists does
Previous:From: PostgreSQL Bugs ListDate: 2004-02-05 12:39:31
Subject: BUG #1073: JDBC "time with time zone"

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group