Обсуждение: NOT IN issue

Hi,

I feel like not having understood something, or maybe I should upgrade ?

simplified setup:

   CREATE TABLE personne (id SERIAL, PRIMARY KEY(id), UNIQUE(id));
   CREATE TABLE utilisateur_news (id INT4 REFERENCES personne);
   INSERT INTO personne VALUES(1);
   INSERT INTO personne VALUES(2);
   INSERT INTO personne VALUES(3);
   INSERT INTO utilisateur_news VALUES(2);

the above works without any problem with the two NOT IN and EXISTS
case.

   SELECT id
   FROM personne p
   WHERE p.id NOT IN (SELECT DISTINCT un.id
                      FROM
                      utilisateur_news un);

this is working properly:

   SELECT p.id
   FROM personne p
   WHERE NOT EXISTS (SELECT un.id
                     FROM utilisateur_news un
                     WHERE (un.id = p.id));

Now, I have a database in production that is slightly more complex but has
the same types for id, and in that database, the NOT IN FORM doesn't
return any results, but the EXISTS does.

This is with 7.1release-3.potato.1, a Debian test package.

Is my SQL code correct ? Any idea ?

Thanks.

Perhaps there are some NULLs in the sub-SELECT's result?  See the archives
concerning the not-very-intuitive behavior of NOT IN with NULL.

            regards, tom lane