Thread: Is NULL equal to NULL or not?

jco@cornelius-olsen.dk wrote:
>
> Hi,
>
> My first message on this list:
>
> Using PostgreSQL 7.1.3.
>
> I've noticed that
>         select NULL=NULL;
> yields TRUE. It is also possible to select rows in this manner.

Not generally since 7.2:

http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/release-7-2.html

"In this release, comparisons using = NULL will always return
false (or NULL, more precisely). Previous releases automatically
transformed this syntax to IS NULL. The old behavior can be
re-enabled using a postgresql.conf parameter."

> At the same time in PL/pgSQL:
>
> drop function demo1();
> create function demo1() returns boolean as '
> declare
>  a boolean:=NULL;
>  b boolean:=NULL;
> begin
>  return a=b;
> end;
> ' language 'plpgsql';

Not sure why that is. Different sub-parsing paths, perhaps?
However, the default has changed since the old days (7.1). The
hack was in place because ancient versions of Access (re: Access
95) used to call into the ODBC driver with parameters which
resulted in PostgreSQL getting queried with:

SELECT
...
WHERE field = NULL;

That behavior changed in Microsoft products a long time ago, so
the PostgreSQL behavior now mirrors the SQL standard. So from
this point forward, I'd code using IS NULL for NULL comparisons.
And I'd upgrade... ;-)

Hope that helps,

Mike Mascari

P.S.: NULLs are evil.

On Mon, 9 Dec 2002 jco@cornelius-olsen.dk wrote:

> Hi,
>
> My first message on this list:
>
> Using PostgreSQL 7.1.3.

Stop.  Upgrade right now.  You're asking questions about a version of
postgresql that is quite old now.  I'm not sure I remember using 7.1.3,
but since I started out with 6.5.2, I'm sure I must have.  But it's been a
long long long time since I've used either, and I certainly don't have a
test box laying around running it.  So I can't test anything on my end to
see how it works.

However, on 7.3, select 1 where null=null; gets me a 0 output.  Same goes
for 7.2.3.

> 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.

> At the same time in PL/pgSQL:
>
>         > drop function demo1();
>         create function demo1() returns boolean as '
>         declare
>           a boolean:=NULL;
>           b boolean:=NULL;
>         begin
>           return a=b;
>         end;
>         ' language 'plpgsql';
>
>         > select demo1();
> yields NULL. Yet if I'd written "return NULL=NULL" I would have gotten the
> same result as in the simple query;

Try a line like this:

return a is null;

and see what you get.  Use the verb is, not the = sign to compared nulls.
NOT is to decide it not null.

scott.marlowe wrote:
> On Mon, 9 Dec 2002 jco@cornelius-olsen.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. ':=' is the PL/pgSQL assignment operator.
Older versions of PostgreSQL allowed comparisons between NULLs
for compatibility with older versions of Access.

Mike Mascari

On Mon, 9 Dec 2002, scott.marlowe 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.

  This is a common misuderstanding. NULL means indeterminant, not zero or
missing. Since NULL is and unknown value there's no way to compare it with
another unknown value. You are absolutely correct in that the syntax 'are
both values unknown' equates to NULL is NULL while there is no meaning to
NULL=NULL.

Rich

Dr. Richard B. Shepard, President

                       Applied Ecosystem Services, Inc. (TM)
            2404 SW 22nd Street | Troutdale, OR 97060-1247 | U.S.A.
 + 1 503-667-4517 (voice) | + 1 503-667-8863 (fax) | rshepard@appl-ecosys.com
                         http://www.appl-ecosys.com/

Mike Mascari <mascarm@mascari.com> writes:
>> On Mon, 9 Dec 2002 jco@cornelius-olsen.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