Thread: BUG #4816: NOT IN clause, doesn't work
The following bug has been logged online: Bug reference: 4816 Logged by: jose soares Email address: jose.saores@sferacarta.com PostgreSQL version: 8.3.5 Operating system: x86_64-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian 4.3.2-1) 4.3.2 Description: NOT IN clause, doesn't work Details: Hello all, I'm trying a NOT IN clause, but seems it doesn't work. Please take a look: mydb=# select distinct id from psop where id_ua = 72492; id ------- 56844 67953 67955 (3 rows) mydb=# select distinct id_sop from scad where id_ua = 72492; ---------------- 56844 67953 (3 rows) mydb=# select distinct id from psop where id_ua = 72492 and id not in (select distinct id_sop from scad where id_ua = 72492); id ---- (0 rows) mydb=# select distinct id from psop where id_ua = 72492 and id not in (56844,67953); id ------- 67955 (1 row) What's wrong with those selects? j
jose soares escribió: > The following bug has been logged online: > > Bug reference: 4816 > Logged by: jose soares > Email address: jose.saores@sferacarta.com > PostgreSQL version: 8.3.5 > Operating system: x86_64-pc-linux-gnu, compiled by GCC gcc-4.3.real > (Debian 4.3.2-1) 4.3.2 > Description: NOT IN clause, doesn't work > Details: > > Hello all, > > I'm trying a NOT IN clause, but seems it doesn't work. Please take a look: > > > mydb=# select distinct id from psop where id_ua = 72492; > id > ------- > 56844 > 67953 > 67955 > (3 rows) > > mydb=# select distinct id_sop from scad where id_ua = 72492; > ---------------- > 56844 > 67953 > > (3 rows) > > mydb=# select distinct id from psop where id_ua = 72492 and id not in > (select distinct id_sop from scad where id_ua = 72492); > id > ---- > (0 rows) > > mydb=# select distinct id from psop where id_ua = 72492 and id not in > (56844,67953); > id > ------- > 67955 > (1 row) > > > What's wrong with those selects? > > j > > Hola, el problema me parece que lo introduce algun nulo en el valor de id. como no podemos ver las estructuras y el juego de datos , estamos limitados a suposiciones como la que te envio Saludos Fernando
On Wed, May 20, 2009 at 09:37:56AM +0000, jose soares wrote: > I'm trying a NOT IN clause, but seems it doesn't work. Please take a look: the problem you're experiencing is described in here: http://www.depesz.com/index.php/2008/08/13/nulls-vs-not-in/ Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007