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

On Wed, 14 Dec 2005 15:08:23 -0800, Gene Wirchenko wrote:

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

Hi Gene,

Agreed. That's why I was reminded of the 'zero * infinity' debate.

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

My bas. I intended to use a column reference or variable that equals NULL. Since both NULLS come from the same variable/column, we do have a way of knowing that they 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.

AFAIK, all ANSI standards since '92 specify that NULLS propagate in all scalar expressions (with -unfortunately- the notable exception of the boolean datatype introduced in SQL-99) and that NULLS are excluded in all aggregate functions.

Works for me.

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

Has there _ever_ been a short thread about NULLS in this group?

QED Best, Hugo

-- 

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