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

On Wed, 14 Dec 2005 23:10:28 +0100, Hugo Kornelis <hugo_at_pe_NO_rFact.in_SPAM_fo> wrote:

[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.

     But NULL*0 not being zero is also doing that.

>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')

     No in both cases, because we have no way of knowing whether the two nulls are denoting the same value.

>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!

     NULL does that.

>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.

     It is not that predictable. Consider the length of this thread.

Sincerely,

Gene Wirchenko Received on Thu Dec 15 2005 - 00:08:23 CET