Re: When is a record NULL?

Sam Mason <sam@samason.me.uk> wrote: 
> On Fri, Jul 24, 2009 at 12:30:39PM -0500, Kevin Grittner wrote:
> In this dichotomy a NULL is most definitely a value and with my
> current experience I don't understand the distinction you're trying
> to draw.
There can be a place where a value *could* go which does not contain a
value.  Codd considered it crucial, from a mathematical correctness
point of view, that the absence of a value not be indicated by some
special "magic value", but rather by some other technique which
indicates that there *is* no value there.  In SQL this is done with
NULL.  Based on reading his books, it seems to me that Codd always
seemed uncomfortable with this, since it made it appear to be some
special value, which he was adamant that it is *not*.  It seems he
would have preferred a relational language use a term like "FLAGGED AS
MISSING" rather than "IS NULL".  It also would have allowed the
flexibility to differentiate various types of missing values, such as
"FLAGGED AS UNKNOWN" or "FLAGGED AS NOT APPLICABLE".
>> The distinction between not having a tuple and having a tuple for
>> which you don't know any applicable values seems thin.  I'm not
>> sure what that would really mean.
> 
> Other languages/type systems do define this precisely.
Yeah, I've made my living programming for decades, and worked in
dozens of languages, so I know how this is usually done.  I do think
that set logic in relational data involves some slightly different
twists on things than most language have.  I tend, for bettor or
worse, to come down in agreement with the positions Codd espoused on
most of these things.
> [PG] ... internally knows there is a distinction
> between the two but it doesn't like to expose this.
Well, to some extent I think it's a tough problem, since the set logic
of a relational database is implemented in C, which doesn't have the
same concepts.  There's got to be a little slight of hand in there
somewhere.
> If your model is correct then when the IS DISTINCT FROM operator
> works on RECORDs the following should return FALSE for all of the
> following:
> 
>   SELECT NULL      IS DISTINCT FROM ROW(NULL);
>   SELECT NULL      IS DISTINCT FROM ROW(NULL,NULL);
>   SELECT NULL      IS DISTINCT FROM ROW(NULL,ROW(NULL,NULL));
>   SELECT ROW(NULL) IS DISTINCT FROM ROW(NULL,ROW(NULL,NULL));
> 
> i.e. there is *no* difference between a NULL record and a record
> consisting entirely of NULLs.
Well, on that I would go with whatever the SQL standard says, and hope
it's not too ambiguous.  (I haven't tried to sort though this one in
the standard, so far.)  I was going into the theory both because it is
the basis for some of the seemingly odd aspects of SQL, and because at
least half the time I see someone put the word NULL immediately in
front of the word VALUE, they are wandering into confusion on these
issues.  (I will admit that using such technically incorrect language
is sometimes hard to avoid without sounding stilted, even if all
parties to the conversation know that NULL is *not* a value.)
I know that Codd was insistent that any relation (which included the
result of any query) which could contain duplicate rows should be
called a "corrupted relation".  (In fact, in one of his books I think
he averaged a comment on this point about once every two pages.)  So I
shudder to think what his reaction would be to a relation with a row
which contained no values.  I have a really hard time figuring out
what useful information such a row could represent.
-Kevin