Hi,
it is a bug ??

create table test (t1 int4, t2 int4);
insert into test values (1,1);
insert into test values (2,2);
insert into test values (3,1);
insert into test values (4,1);
insert into test values (4,1);
insert into test values (4,null);



    select * from test where t1 not in (select t2 from test);
0 rows
    select * from test where t1 not in (select null);
0 rows

If I delete the row with null value it works as expected.
The IN clause work as expected with or without null row.

sorry for my english.
bye

-- 
-------------------------------------------------------
Giuseppe Tanzilli		g.tanzilli@gruppocsf.com
CSF Sistemi srl			phone ++39 0775 7771
Via del Ciavattino 
Anagni FR
Italy




Thu, 29 Nov 2001 17:25:06 +0100
Giuseppe Tanzilli - CSF <g.tanzilli@gruppocsf.com>
select  NOT IN  with NULL bug   on 7.2b3
On Thu, 29 Nov 2001, Giuseppe Tanzilli - CSF wrote:

> Hi,
> it is a bug ??
>
> create table test (t1 int4, t2 int4);
> insert into test values (1,1);
> insert into test values (2,2);
> insert into test values (3,1);
> insert into test values (4,1);
> insert into test values (4,1);
> insert into test values (4,null);
>
>
>
>     select * from test where t1 not in (select t2 from test);
> 0 rows
>     select * from test where t1 not in (select null);
> 0 rows
>
> If I delete the row with null value it works as expected.
> The IN clause work as expected with or without null row.

I think this falls into the nulls are painful category of
trivalued logic.

IIRC:
When you ask for t1 not in (subselect)
you get : not(t1 in (subselect) -> not(t1 =ANY (subselect))
 -> for each row of subselect does t1 = t2 (in your case)
    * if true for any row, the in returns true (not in returns false)
    * if false for every row, the in returns false (not in - true)
    * otherwise, the in returns unknown (not in - also unknown).
Basically with a NULL, you can say that a row is there definitively
but not that a row is not there since you don't know if the 3 equals
that NULL or not (same for the 4s).



Thu, 29 Nov 2001 08:58:54 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: select  NOT IN  with NULL bug   on 7.2b3
Giuseppe Tanzilli - CSF <g.tanzilli@gruppocsf.com> writes:
> it is a bug ??

No, it isn't.  See past discussions about the semantics of NOT IN and
NULL.

			regards, tom lane

Thu, 29 Nov 2001 12:04:56 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: select NOT IN with NULL bug on 7.2b3 