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

From: David Cressey <david.cressey_at_earthlink.net>
Date: Sat, 19 Nov 2005 14:19:21 GMT
Message-ID: <JvGff.1982$wf.1970_at_newsread3.news.atl.earthlink.net>


"Marshall Spight" <marshall.spight_at_gmail.com> wrote in message news:1132329245.953620.251950_at_g44g2000cwa.googlegroups.com...

> Just so. What I was trying to deflect was not the idea that
> SQL's null has well defined semantics. I *does* have well-defined
> semantics; I just don't happen to like the definition. Rather,
> I was trying to avoid any conversation about whether this-or-that
> semantics for null (independent of SQL per se) was "right."
> There is no "right"; there are only qualities like usefulness
> or consistency.

I'm wondering if you can reframe your objection, perhaps as follows: you have a need for a token marking some exception, and SQL NULL doesn't meet that need, because of the way SQL NULL is defined.

I don't mean to put words in your mouth, but it seems to me that the above is more useful than quarreling with the definition as stated.

> > If you take NULL as meaning "no opinion", per our previous discussion,
it
> > makes perfect sense.
> >
> > eval ( NULL + 43) = NULL.
>
> I don't see how it makes perfect sense. Rather, it just strikes me
> as bluntly inconsistent. Why does summation behave one way
> "vertically" and a different way "horizontally?" I consider this
> inconsistency bad design. Vertically you get the "empty"
> semantics; horizontally you get the "unknown" semantics.
>

I'm guessing about what you mean by "vertically" and "horizontally", but here goes.

First, even in relational tables, rows don't behave the same way columns do. In particular, to add a new column, you have to do an ALTER TABLE, and that's DDL. To add a new row, you do an INSERT, and that's DML.

Second, I'm not sure how NULL behaves in the context you mention. Let's take an example.

SELECT AVG(SALARY) FROM EMPLOYEES; I chose AVG rather than SUM advisedly. AVG applies to a set of numbers, and NULL will be excluded from the set.

On the other hand (NULL + 43) cannot be evaluated. You can't just say that it's a set of one number. The semantics state that there are two numbers to be summed, and one of them is missing. At least, that's how I read it.

> Yeah, that would be cool. We have had a few Pick advocates come
> through here, and while they have raised some interesting points,
> I haven't been able to construct a clear model of their system
> from their posts. I've never been able to find good reading about
> the topic, either.

A lot of the Pickies answers boil down to "the programmer doesn't need to know that". That reminds me of "the MacIntosh user doesn't need to know that." It could be true, but if I need to know it, that's not a very satisfying answer.

If I've read some of Dawn's posts correctly, it seems that Pick treats a set of only one element and the element thus specified as the same thing. That is, {1} = 1. That's a recipe for disaster, as far as I'm concerned. If it's not a recipe for disaster, then I need a carefully reasoned explanation of why it's not. Received on Sat Nov 19 2005 - 15:19:21 CET

Original text of this message