Many discussions point out one deficiency of NULLs: that they collapse
multiple, distinct concepts into one ("no value possible," "value missing,"
"value not available at this time", etc).

What are the other theoretical problems? My impression from skimming some
threads in this ng is that some anomalies might occur, maybe having to do
with NULLs and joins, or NULLs and keys composed of more than one field, but
I'm not sure.

sinister wrote:

The ultimate theoretical problem is a complete lack of any theory
underpinning NULL.

On 8 aug, 14:26, Bob Badour <bbad... (AT) pei (DOT) sympatico.ca> wrote:
Just to avoid any misunderstandings: there has of course been lots of
theory on certain interpretations of null values, such as the work by
Raymond Reiter and by Joachim Biskup, but not on the specific meaning
(if you can call it that) that they were given in SQL. Whether that is
necessarily a big problem is IMO not so easy to say.

-- Jan Hidders

Jan Hidders wrote:

In other words, some folks accept that NULL exists without any
theoretical underpinning and then create theories of interpretation. How
exactly does that differ from scriptural interpretation and theories
thereof?

Bob Badour <bbadour (AT) pei (DOT) sympatico.ca> wrote:

[snip]

Null: computing science's da Vinci Code.

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

"sinister" <sinister (AT) nospam (DOT) invalid> wrote

--Kris Kristofferson --

On Aug 8, 7:20 am, Jan Hidders <hidd... (AT) gmail (DOT) com> wrote:
Yes; here we run into the complete lack of any theory of whether
something is necessarily a big problem or not.


Marshall

PS. It was funnier in my head.

On 8 aug, 17:09, Bob Badour <bbad... (AT) pei (DOT) sympatico.ca> wrote:
Indeed. Because, as we all know, proposing and investigating
alternatives is the same as accepting something's existence.

Exactly! Rejecting straight away null values in any form or shape
without any sort of investigation of their properties would have been
much more scientific. :-)

-- Jan Hidders

Jan Hidders wrote:

That doesn't answer the question. How does it differ from scriptural
interpretation and theories thereof?

On Wed, 8 Aug 2007 06:43:53 -0400, sinister wrote:

Hi sinister,

The deficiency in these discussions is that they fail to normalize the
design before starting to look at NULLs.

The ANSI standard defines NULL as follows:

"null value: A special value that is used to indicate the
absence of any data value."

Thus, the only concept collapsed into NULL is that of "no value here".

The concepts you mention are, indeed, various reasons why a value might
be missing. The specific reason might or might not be of interest to the
users of a database. If it's not, there's no need to let it bother us;
if it is, we need to properly model it.

<Sidestep>

How would you react if someone showed you a database model where the
contracts table had a single column to store both termination date and
the reason why the contract was terminated - I'm pretty sure that you'd
send the junior DBA who made that model back to class to study
normalisation rules, right?

How then would you react if you saw a model that tries to combine the
termination date and the reason why the contract is NOT terminated in a
single column?

</Sidestep>

And yet, that is exactly what some people try to do in the discussion of
NULLs. They see that a value might be missing (NULL) for various
reasons, which is of course correct. They assume that the reason why the
value is missing should be somehow stored or encoded in the database,
which is not necessarily correct but might be requireed for some
applications. And then they go on to state that this reason should be
stored or encoded in the same column where the missing value should have
been - i.e. they want to use a single column to store both the
termination date of a terminated contract and the reason why there is no
termination date of a (probably not terminated, though that's not even
sure) contract. And that is of course a violation of 1NF.

Unfortunately, many database theorists, including Codd and Date, either
failed to see this basic flaw in the discussion or deliberately
sidestepped it for convenience. This has led to many pointless
discussions on using A-marks and I-marks, and how that would lead to
fourvalued logic and still not suffice, and so on....

I made this same point, but in different words, about a month ago on my
blog:
http://sqlblog.com/blogs/hugo_kornel...lack-hole.aspx

Best, Hugo