[BUGS] BUG #4422: select ... where ... NOT EXISTS / NOT IN

The following bug has been logged online:

Bug reference: 4422
Logged by: vasile
Email address: vasile@gmail.com
PostgreSQL version: 8.2.4
Operating system: Centos 4
Description: select ... where ... NOT EXISTS / NOT IN
Details:

I have this 2 similar queries.

Why the 1st query is returning 1000+ rows and the 2nd one no rows ?
The col1 is not empty in both tables.

1)
SELECT col1, col2, col3
FROM table1 t1
WHERE NOT EXISTS (SELECT t2.col1 FROM table2 t2 WHERE t1.col1 = t2.col1 );

2)
SELECT col1, col2, col3
FROM table1 t1
WHERE t1.col1 NOT IN (SELECT t2.col1 FROM table2 t2 );

If I build the query with "LEFT JOIN" I have the same result set like in the
1st query:

SELECT t1.col1, t1.col2, t1.col3
FROM table1 t1
LEFT JOIN table2 t2 ON (t1.col1 = t2.col1)
WHERE t2.col1 is null;

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs