Re: comparing NEW and OLD (any good this way?) - Mailing list pgsql-general

From Sam Mason
Subject Re: comparing NEW and OLD (any good this way?)
Date
Msg-id 20090817151744.GF5407@samason.me.uk
Whole thread Raw
In response to Re: comparing NEW and OLD (any good this way?)  ("Daniel Verite" <daniel@manitou-mail.org>)
Responses Re: comparing NEW and OLD (any good this way?)  ("Daniel Verite" <daniel@manitou-mail.org>)
List pgsql-general
On Mon, Aug 17, 2009 at 03:45:02PM +0200, Daniel Verite wrote:
> while the spec says that if X is the null value, then "X is null"
> evaluates to true, it doesn't say that if "X is null" is true, then X
> is the null value. And that's the catch.

But you've had to introduce a whole new abstraction (that there is this
difference) just to make sense of the spec and PGs implementation of it.
Most people struggle with NULL enough, it's madness to introduce another
layer to say that when something says it's NULL it's not actually NULL.

> Once digested the (counter-intuitive) rule that "X is null" should never be
> confused with "X is the null value", then PG's behavior suddenly feels
> consistant.

Because there's a hack in there to make RECORDs special.  Apart from
them IS NULL is completely polymorphic with respect to the datatype it's
operating over.  Internally there's a nice structure to track what's
*really* NULL and what's not, this is reported on for *everything*
except RECORDs.

I've just realized another case where it's not consistent; why does the
following return true:

  SELECT row(null) IS NULL;

and yet the following false:

  SELECT row(row(null)) IS NULL;

> I believe the implication of this weirdness for SQL programmers is that when
> we feel like using "IS NULL" and rowtypes are involved, we should think hard
> about what we really want to test and possibly use "IS DISTINCT FROM NULL"
> rather than "IS NULL".

So when is IS NULL ever to be used then?  I don't think I've ever
written code that uses IS NULL the way that the spec defines it.  I've
wanted "v IS NULL" to mean the same as "v IS NOT DISTINCT FROM NULL",
this being the same as "NOT (v IS DISTINCT FROM NULL) lots of times, but
if I'm interested in knowing if a member of a RECORD is NULL then I want
to know specifically which attribute it is.


I think I'm saying that PG should be deliberately breaking specified
behavior and go back to pre-8.2 behavior in this regard.

--
  Sam  http://samason.me.uk/

pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: plpython return setof and yield
Next
From: Sam Mason
Date:
Subject: Re: Generating random unique alphanumeric IDs