Re: [BUGS] (null) != (null) ?

Todd Vierling <tv(at)pobox(dot)com> writes:
> (1) SELECT ... FROM table1 a,table2 b WHERE a.fieldname = b.fieldname;

> Both "fieldname" definitions are identical (verified with char(2) and
> varchar(100) in particular), and both tables contain a row with a "null" in
> that field. However, the results don't contain the row with the "null"
> value.

NULL = NULL does not yield TRUE, it yields NULL. For that matter,
NULL != NULL does not yield FALSE --- it yields NULL. This is a
basic consequence of the semantics of NULL. The easiest way to
think about NULL that I've heard of is: "NULL means I don't know
what the value should be". So, for example, NULL = NULL is asking
whether two things are equal when you don't know exactly what
either of them is. The answer cannot be "yes", it cannot be "no",
it has to be "I don't know" --- ie, NULL.

Nearly all Postgres operators yield NULL if any input is NULL.
This is perfectly sensible; for example, if you don't know what
x is, you don't know what x+1 is, either. The main exceptions
are the special operators IS NULL and IS NOT NULL. I think we also
put in a dirty hack to treat "x = NULL" (when NULL is written as a
literal constant) as "x IS NULL", because some clueless programmer
at Microsloth made MS SQL act that way, and now people expect it.
But it's bogus by any strict interpretation :-(

The WHERE clause treats a NULL test result as false (ie, the row
doesn't get selected), which accounts for the behavior you cite.
A really hard-line view of the semantics would be that WHERE NULL
should raise an error --- after all, if you don't know the result
of the test, how can you say if the row should be in or out? But I
guess the SQL committee felt that that would be sacrificing too much
usability in the name of logical purity. If it worked that way you
could hardly ever write a WHERE clause without explicit tests for
NULLs.

If you really want to match up nulls in your example, you can do
something like
WHERE (a.fieldname = b.fieldname) OR
(a.fieldname IS NULL AND b.fieldname IS NULL)
This is pretty grotty, of course, so my inclination would be to
use a special non-NULL value --- an empty string, for example ---
for rows that you wanted to match like this.

PS: The above WHERE does succeed where both fields are NULL.
Exercise for the student: explain why. (Hint: OR is just a
little bit special.)

> (2) NOT IN doesn't seem to work at all. I always get 0 results--and very
> rapidly at that!--regardless of the situation.

I don't think it's quite *that* broken. How about a concrete
example of what you're trying to do?

regards, tom lane