Обсуждение: BUG #4816: NOT IN clause, doesn't work

Поиск
Список
Период
Сортировка

BUG #4816: NOT IN clause, doesn't work

От
"jose soares"
Дата:
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

Re: BUG #4816: NOT IN clause, doesn't work

От
Rodriguez Fernando
Дата:
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

Re: BUG #4816: NOT IN clause, doesn't work

От
hubert depesz lubaczewski
Дата:
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