Re: Is NULL equal to NULL or not?

Mike Mascari <mascarm(at)mascari(dot)com> writes:
>> On Mon, 9 Dec 2002 jco(at)cornelius-olsen(dot)dk wrote:
>>> I've noticed that
>>> select NULL=NULL;
>>> yields TRUE. It is also possible to select rows in this manner.
>>
>> That's NOT because null EQUALS null, but because it allowed you to assign
>> one null value to another. which technically can be done, so it came out
>> true.

> It *is* because null EQUALS null, on version 7.1. '=' is the
> comparison operator.

Not quite ... thanks for playing.

NULL has never equaled NULL, in any version of PG AFAICT --- the
comparison operator would always yield NULL if either input was NULL.
That behavior is per SQL spec.

Beginning in release 6.5 there was a special kluge for Access
compatibility, originally this two-line patch:

1999-03-14 00:14 momjian

* src/backend/parser/: gram.c, gram.y: I have a problem with
Access97 not working properly when entering new records using a sub
form, i.e. entering a new order/orderlines or master and detail
tables. The problem is caused by a SQL statement that Access97
makes involving NULL. The syntax that fails is "column_name" =
NULL. The following attachment was provided by -Jose'-. It
contains a very small enhancement to gram.y that will allow
Access97 to work properly with sub forms. Can this enhancement be
added to release 6.5?

<<gram.patch>>
Thanks, Michael

which basically hacked the parser so that the syntax

expression = NULL

would be treated as

expression IS NULL

But note that this is a *parse time* transformation, and would only
fire when the right-hand operand of '=' is the literal keyword NULL.
(Thus, it does not fire in jco's plpgsql example, where the operands
are both plpgsql variables.)

This should never have been made the default behavior IMHO, and
certainly not the sole behavior, because it blatantly broke SQL
compatibility, not to mention violating any usable concept of what
NULL means. As of 7.2 it's a disabled-by-default option, which is
the way it should have been handled to begin with (though I suppose
the lack of a flexible runtime-parameter-configuration mechanism
back then might have had something to do with the original decision).
But 6.5, 7.0, and 7.1 lasted long enough to allow a lot of people to
become confused :-(

NULL is not "=" to NULL. It is not "<>" NULL, either. It's off in
its own little universe. Except for a very small number of special
syntaxes like "x IS NULL" and "x IS NOT NULL", any standard SQL
operation applied to one or more NULL inputs will yield a NULL output.
In particular equality comparison will yield NULL.

(It may help you to realize that "IS" is not a kind of equals operator
here --- rather the entire phrase "IS NULL" or "IS NOT NULL" is a
special operator. For comparison, "x IS 3" is not an acceptable way of
spelling "x = 3".)

regards, tom lane