Re: NULLs: theoretical problems?
Date: Sat, 01 Sep 2007 13:36:53 -0300
Message-ID: <46d994d9$0$4021$9a566e8b_at_news.aliant.net>
Keith H Duggar wrote:
>>Keith Duggar wrote:
>>
>>
>>>I'm not trying to have anything. I'm trying to understand what
>>>the "write DEF" prescription buys us over say the "Interactive
>>>Mathematical Proof System" of Farmer, Guttman, and Thayer that
>>>has exactly the property I described that any formula is false
>>>if any variable is NULL. Again, I am failing to grasp what the
>>>"write DEF" prescription buys us. I would like to understand.
>>
>>>KHD
>>
>>Error detection. It forces one to acknowledge that one understands the
>>attribute can be undefined and that one is ignoring those propositions
>>for which it is undefined.
>
>
> So one kind of error detection it allows is checking that a
> user understands an attribute can be undefined? How helpful
> would that be in preventing common query errors? Or does it
> allow error detection beyond NULL understanding checks?
>
> Thanks for help.
>
> KHD
It would be very helpful. Take my favourite example:
SUM(A) + SUM(B) = SUM(A+B) The above identity does not hold in SQL.
(SELECT SUM(A) FROM T) + (SELECT SUM(B) FROM T)
does not equal (SELECT SUM(A+B) FROM T)
because the terms are really:
SELECT SUM(A) FROM T WHERE DEF T.A : TRUE SELECT SUM(B) FROM T WHERE DEF T.B : TRUE SELECT SUM(A+B) FROM T WHERE DEF T.A AND DEF T.B : TRUE Being forced to acknowledge the potentially undefined highlights the difference. While that is just one example of a common pitfall, I use it because the accountants don't like it when the numbers don't balance.
To balance, one really has to write the original identity as: SUM(ISNULL(A,0)) + SUM(ISNULL(B,0)) = SUM(ISNULL(A,0) + ISNULL(B,0)) (Presumably under Jan's model a similar ISUNDEF or COALESCE operator will exist.)
SQL doesn't give a warning or an error. It just returns incorrect answers, and naive users are reassured that SQL handles missing information because it has NULL.
While it might be tedious to do it right the first time, it costs the organization a whole lot more when the external auditor complains than when the compiler complains. Received on Sat Sep 01 2007 - 18:36:53 CEST