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

In article <1134777022.204960.239330_at_f14g2000cwb.googlegroups.com>, boston103_at_hotmail.com says...
>
> Jon Heggland wrote:
> >
> > I'm not sure what you mean... but if IS_NULL(x) is TRUE, all expressions
> > involving x will also cause IS_NULL() to return TRUE, no matter what
> > operations are involved---if that is what you mean by "fail".
>
> So nothing really changes except the notation ?

Funny story (well, sort of:): Right after posting the above, I thought: Where does this leave the UNKNOWN truth value? Here's how the internal dialogue went:

Jon 1: Hey, if all expressions with null subexpressions turns out null, how do you ever get your UNKNOWN truth value?

Jon 2: By Jove! You're right! I guess I'll have to say that comparisons and other truth valued expressions with null subexpressions evaluates to UNKNOWN. Jon 1: But that's not very consistent. Why make a special case for truth values?

Jon 2: Good point; I do like my consistency. Hmm... Actually, I think I'll drop the third truth value, and just have TRUE and FALSE. I'll call this domain "Boolean". Of course, any expression can still be null.

Jon 1: Okay... You still have the problem that "TRUE OR x" and "FALSE AND x" are null instead of TRUE and FALSE, respectively, if IS_NULL(x) is TRUE. That doesn't make sense.

Jon 2: True, true. The system will indeed produce wrong answers sometimes if this is the case. To be able to detect such expressions in general seems very hard though. Consider "substring(s, length(s))"---it should always evaluate to "", but is it feasible to create an algorithm for detecting such things? I wouldn't be surprised if that problem was isomorphic to tautology detection. That said, it may be reasonable to make special allowances for the simplest cases: AND and OR with FALSE and TRUE. I can make those exceptions for null propagation (and perhaps use a table or two to illustrate how it works?).

Jon 1: In that case, isn't what you've got SQL-99?

Jon 2: Jehoshaphat! That's right!! I have reached SQL enlightenment!!! I guess Codd and the SQL committee made sense after all! Well, their solution may not be ideal, but at least now I understand their reasons and motivation. :)

So yes, nothing really changes except the notation and the explanations. I never use NULL as a noun, or the name of a value; only as an adjective. No literal expression can possibly be null---and to use tables looking very much like truth tables to explain the exceptions to null propagation is therefore not possible, despite Jon 2's musings above. :)

-- 
Jon