Re: NULLS and <> : Discrepancies ?

On Fri, 22 Dec 2000, Emmanuel Charpentier,,, wrote:

> Could some kind soul explain this to me ?
>
> test1=# select distinct "Cle" from "Utilisateurs";
> Cle
> -----
> 1
> 2
> 3
> 4
> (4 rows)
>
> test1=# select distinct "CleUtil" from "Histoires";
> CleUtil
> ---------
> 1
>
> (2 rows) -- Uuhhh !
>
> test1=# select count(*) as NbRec from "Histoires" where "CleUtil" is null;
> nbrec
> -------
> 2
> (1 row) -- Ah Ahh ... I have NULLs.
>
> test1=# select distinct "Cle" from "Utilisateurs" where "Cle" in
> test1-# (select distinct "CleUtil" from "Histoires");
> Cle
> -----
> 1
> (1 row) -- That's OK ...
>
> test1=# select distinct "Cle" from "Utilisateurs" where "Cle" not in
> test1-# (select distinct "CleUtil" from "Histoires");
> Cle
> -----
> (0 rows) -- That's definitively *NOT* OK ! However
>
> test1=# select distinct "Cle" from "Utilisateurs" where "Cle" not in
> test1-# (select distinct "CleUtil" from "Histoires" where "CleUtil" is
> not null);
> Cle
> -----
> 2
> 3
> 4
> (3 rows) -- That's what I expected in the first place.
>
> Could someone explain to me why not eliminating nulls destroys the
> potential results of the query ? In other words, for any X not null, X
> not in (some NULLs) is false.

It's probably actually not false but unknown. SQL uses a three valued
logic system, true, false and unknown and A=B is unknown if either A
or B is null.

If I'm reading the spec correctly:
A NOT IN B -> NOT (A IN B) -> NOT (A = ANY B)
and for A = ANY B the rules say, for any element RB in B if A=RB is
true then A = ANY B is true. if A=RB is false for *all* elements
RB in B then A = ANY B is false, otherwise it is unknown.

And for where tests, it returns rows where the where test is true,
but in this case the where test is unknown for those rows you
expect to show up.