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

On 14 Dec 2005 18:54:54 -0800, vc wrote:

>
>Hugo Kornelis wrote:

(snip)
>> 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.
>
>It's enough to know that the number of elements of every finite Boolean
>algebra is a power of two in order to figure out that a tree element
>algebra ain't Boolean.

Hi vc,

Thanks for confirming my suspicions.

(snip)
>> I say that "there should be no difference" becuase we agreed (or at
>> least you didn't object),
>
>I did not notice that.
>
>>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."
>
>Really ? What about 'FALSE AND NULL' evaluating to FALSE, or 'TRUE OR
>NULL' evaluating to TRUE ? See the original Codd's truth tables.

AFAIK, there is no NULL in the original Codd's truth tables. Only True, False and Unknown.

(snip)
>> 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.
>
>Predictability at the expense of making little or no sense ?

Yes. I'd like the behaviour of NULLS to make sense AND be predictable as well. But I don't think it's possible to make that happen. It's easy to redefine NULL behaviour for the "easy" situations, such as 0 * NULL or TRUE OR NULL. But how to handle the more complex cases? There will be a point where the expression is too complex, and the DBMS has to revert to the standard behaviour. The result is that you still are not guaranteed to get results that make sense, but they're not predictable either, so it's nigh on impossible to work around the shortcomings.

Total NULL propagation is much easier. Any trained coder should know how NULLS behave, and since this behaviour is totally predictable, it's easy to work around the nonsensical aspects of it.

>> 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.
>
>Ah, but that's different, because the first NULL may represent some
>other value that the second NULL and you cannot factor it(them) out.

See my reply to a similar remark by Gene. I changed something here just before sending, but forgot to complete the change.

What I meant was something like

SELECT (2 * Col1 - 2 * Col1)
FROM (SELECT CAST(NULL AS int) AS Col1) AS Derived

Best, Hugo

-- 

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