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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
