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

On 11 Dec 2005 18:23:17 -0800, vc wrote:

>
>Hugo Kornelis wrote:
(snip)
>> If a data type accomodates not only True and False but Unknown as well
>> (represented in whatever way), I'd hesitate to call it Boolean, since
>> most people associate the term Boolean with the most simple Boolean
>> algebra, that has only True and False. But on the other hand, if all
>> rules for a Boolean algebra are met, there's technically no reason to
>> object to the name Boolean for this data type.
>
>Even though, their logic has three truth values ? How can one say
>that there is "no reason to object to the name Boolean for this data
>type" ? You are contradicting yourself.

Hi vc,

I don't think I am.

The mathematical term "Boolean algebra" is NOT limited to the two-valued set {TRUE, FALSE}. This is the most simple Boolean algebra, but there are other, bigger sets that also abide by all the rules for a Boolean algebra.

Check http://mathworld.wolfram.com/BooleanAlgebra.html for a full list of all requirements that must be met in order to qualify as a Boolean algebra. Note especially this line:

"6. There are at least two distinct elements in the set B."

Note that I didn't check if all rules apply for the 3VL set {TRUE, FALSE, UNKNOWN} and the truth tables that come with it. I only say that IF all the rules apply, than 3VL is indeed a Boolean algebra.

Of course, in the field of computing hardly anybody knows this; everyone in this field will only think of the {TRUE, FALSE} set when the term Boolean is used. That's why *I* will never use the term Boolean when discussing 3VL, and why I do think that the choice of the term Boolean in SQL-99 is a bad choice.

(snip)
>> >> But if we have to define seperate results for FALSE AND TRUE, FALSE AND
>> >> UNKNOWN, FALSE AND FALSE, and FALSE AND NULL, then we are clearly no
>> >> longer dealing with the original 3VL logic tables that are mentioned in
>> >> rule 3 above. Rather, we are extending them.
>> >
>> >Isn't it possible to say we are dealing with the normal 3VL tables
>> >(without NULL), but that they only apply when there are no NULLs? After
>> >all, NULL is not a value. Smells bad, perhaps, but I don't really see
>> >the difference compared to how other domains deals with NULL.
>>
>> It's good that you see no difference compared to how other domains
>> handle NULL, because there should not be any difference.
>
>What do you mean by "there should be no difference" ? Could you,
>like, substantiate the requirement of there being no difference ? Or
>is it just a matter of convenience, requiring that any expression
>where NULL is present should be avaluated to NULL ?

I say that "there should be no difference" becuase we agreed (or at least you didn't object), a couple of messages ago, that one of the rules that form the foundation of SQL is this

"1. NULLs propagate - any operation that has NULL as one of it's operands results in NULL."

> What's so
>sensible, for example, in demanding that 'zero times NULL' should be
>equal NULL ? Can you imagine any integer value, however unknown or
>temporarily missing, having been multiplied by zero and producing
>anything but zero ?

Heh! That reminds me of a discussion we had with the math teacher in highschool about the two basic rules 'zero times anything at all is always zero' and 'infinify times anything at all is always infinity' - so what is infinity times zero?

But since infinity is not in the integer domain, you are right that zero times any possible value can only yield zero. There is something to be said for allowing this exception to the NULL propagation rule. BUT, there are also some objections:

1. Predictability. The NULL propagation is simple - NULLS in, NULLS out, without any exceptions. Allowing exceptions means that there will be situations where the code does something that the developer didn't expect.
2. Complexity. 'zero times NULL' is easy visible. But it's entirely possible to hide the zero-multiplication beyond recognistion. In fact, I once had a book with lots of different ways to prove that 1 = 2 and similar silly proofs. Most of them used division by zero somewhere in the process - but hidden away so cleverly that it took me hours to find the culprit. If you allow an exception for 'zero times NULL', you'll have to allow an exception for '2 * NULL - 2 * NULL' as well. And for EXTRACT(MONTH FROM CURRENT_TIMESTAMP) * NULL - NULL * POSITION('lm' IN 'abcdefghijklmno')

There will always be some level of complexity where the DB doesn't understand that there a hidden zero multiplication involved. And then you're getting REALLY unpredictable resutls!

>> >
>> >> For me, it's just too many problems for too little gain.
>> >
>
>It's actually possible to express some folks' dream about having a
>'uniform' NULL, although only partially, in all the domains. Say,
>one can use NULL as a truth value and require that 'NULL equals NULL'
>should evaluate to NULL for truth values as well. Then, in order to
>salvage the logical equivalency based on the notion of truth tables
>being the *same*, one can say: 'Oh, and by the way the equality
>predicate when at least one argument is NULL evaluates to NULL in the
>*object language *, but in our metalanguage, where we talk about
>expressions, we have another equallity predicate that evaluates
>'NULL=NULL' to TRUE. I am not sure, though, that much can be gained
>by introducing this 'meta' and 'object' talk.
>Besides, NULL behaviour would still be not quite uniform because, for
>example, TRUE OR NULL would evaluate to TRUE (hopefully).

This is what SQL-99 does, but I don't like it. TRUE OR UNKNOWN has to evaluate to TRUE, but TRUE OR NULL has to evaluate to NULL, as a result of the NULL propagation rule. I know that it's just as illogical as defining 0 * NULL to be NULL, but at least it keeps NULL propagation as predictable as it currently is.

Best, Hugo

-- 

(Remove _NO_ and _SPAM_ to get my e-mail address)