Re: So what's null then if it's not nothing?

From: Christopher Browne <cbbrowne_at_acm.org>
Date: Sun, 20 Nov 2005 12:49:03 -0500
Message-ID: <m3k6f343cg.fsf@mobile.int.cbbrowne.com>

> "mAsterdam" <mAsterdam_at_vrijdag.org> wrote in message
> news:4380531c$0$11064$e4fe514c_at_news.xs4all.nl...

>> JOG wrote:
>> > Even hearing the word "null" is starting to sound embarrassing now,
>> > never mind postulating a definition for it. Surely the field has
>> > reached the point where we all know there are better ways of handling
>> > missing data, but given the tech we have when we get up tomorrow, and
>> > the job at hand, we have to make do. What more is there to say.
>>
>> [Null] is a very misunderstanding prone theme, so it is ideal for the
>> c.d.t. glossary. I wondered if this thread has brought some new
>> insights concerning how to deal with those misunderstandings.
>>
>> This is the current entry for [Null] in the c.d.t. gossary:
>>
>> > [NULL]
>> > Roughly: a special marker that can be put in a place
>> > inside a data structure where an actual value is expected.
>> > Precisely what that marker means varies and there are at
>> > least three possibilities that are sometimes assumed:
>> >
>> > (1) "Unknown value" This means that on the place of the marker
>> > there should actually be a value but this value is not known
>> > at the present time. For example, if a 'name' field in a tuple
>> > describing a person is 'null' then this person will have a
>> > name but we don't know it.
>> >
>> > (2) "Absent value" This means that the property that is
>> > described by the value in question is simply not defined.
>> > For example, if the 'shipping-date' field in a tuple
>> > describing an order is 'null' then the order was
>> > not shipped yet.
>> >
>> > (3) "Whatever SQL says it means" The exact meaning is hard to
>> > summarize briefly, but is a mixture of the previous two
>> > interpretations and involves a value with three truth-values
>> > ('true', 'false' and 'unknown').
>>
>> You have all really thought about how to talk about null.
>> Are there reasons to change the entry?
>>

> Yes.  Definition (3) above should be the lead definition, and should
> explicitly state what the SQL definition is, namely a marker
> indicating the absence of a value.
>
> Defnitions (1) and (2) above should clearly be distinguished as
> INFERENCES (possibly valid) drawn by the reader, rather than
> INDICATIONS conveyed by the NULL itself.  In other words,
> definitions (1) and (2) attempt to communicate WHY a value is not
> present, which goes beyond the purpose for which NULL was invented.

I think I'd be inclined to start with something like (3), with some indication of both the variations on its handling and of the multiple interpretations that may be made.

Thus...

• NULL is a special marker returned by SQL indicating the absence of a value.
• Different SQL implementations handle this marker differently. In some systems, it is treated as being equal to some sort of "empty/blank/zero/FALSE" value. In others, it is handled as a special kind of value that can never be equal to any value.
• There are numerous inferences that may possibly be taken from receipt of a NULL marker, including:
  • The result exists but was not recorded in the database

    Someone may fill in a survey and decline to indicate their gender.     They presumably have one; it just was not recorded.  

• The result had no applicable value

    In the case of the gender field, above, there do exist truly     unusual cases where gender is not well defined, such as where an     individual has the organs associated with *both* genders.

    Alternatively, severe injury could effectively eliminate gender.

    Consider a sales representative who works solely on commission. A     query that searches for annual salaries should find none for this     individual, as they receive no "salary" per se.

• The result is impossible (e.g. - inconsistent with premises)

    "When did you marry your spouse?" has no meaning for someone who     has never been married and has no spouse.

    Asking for the salary of someone who is not employed should find     "nothing," but it is not precisely the same kind of nothing as the     "salary of the 100% commissioned sales rep."

There may be other interpretations of NULL; these are just the most obvious/common...

-- 
(format nil "~S@~S" "cbbrowne" "cbbrowne.com")
http://linuxdatabases.info/info/slony.html
"Real concurrency---in which one program actually continues to
function while you call up and use another---is more amazing but of
small use to the average person.  How many programs do you have that
take more than a few seconds to perform any task?"
-- New York Times, 4/25/89

Received on Sun Nov 20 2005 - 11:49:03 CST