# Re: NULLs: theoretical problems?

Date: Sat, 01 Sep 2007 11:02:43 -0700

Message-ID: <1188669763.669651.189250_at_r34g2000hsd.googlegroups.com>

Bob Badour wrote:

> Keith H Duggar wrote:

*> > Bob Badour 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.
*

** SELECT SUM(ISNULL(A,0) + ISNULL(B,0)) FROM T
**
2)

** SELECT SUM(A+B) FROM T WHERE DEF T.A AND DEF T.B : TRUE
**
where under 1) accounts balance and under 2) they do not balance
but the maintainers chose this (or were drinking butter beers on
the job) so the DBMS happily obliges.

Is that the point?

**KHD
**
Received on Sat Sep 01 2007 - 20:02:43 CEST