From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | Giuseppe Tanzilli - CSF <g(dot)tanzilli(at)gruppocsf(dot)com> |
Cc: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: select NOT IN with NULL bug on 7.2b3 |
Date: | 2001-11-29 16:58:54 |
Message-ID: | 20011129085325.N43838-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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).
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2001-11-29 17:04:56 | Re: select NOT IN with NULL bug on 7.2b3 |
Previous Message | Bruce Momjian | 2001-11-29 16:51:38 | Re: Second call for platform testing |