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

On Fri, 9 Dec 2005 23:54:33 +0100, Jon Heggland wrote:

>In article <mjvjp1p04cks0cimjmep5uvh8g9ek4p45n_at_4ax.com>,
>hugo_at_pe_NO_rFact.in_SPAM_fo says...
(snip)
>> We'll first have to agree on what the foundations are. To me, the
>> following rules are part of SQL's foundation:
>> [8<]
>> 3. Each predicate evaluates to one of the three values defined in 3VL
>> and each operation with 3VL valued operands results in a 3VL valued
>> result according to the 3VL logic tables.
>
>This one seems to be the problem. You'd need to postulate that any
>expression can be NULL, regardless of its type. Which I think is
>reasonable anyway---why make an exception for truth value expressions?

Hi Jon,

I must admit to being guilty of sloppy writing. Since rule 2 already specifies that NULL should be permitted in 3VL valued columns, it is logical to extend rule 3 to

3. Each predicate evaluates to one of the three values defined in 3VL or to NULL and each operation with 3VL valued operands results in a 3VL valued result or NULL according to the 3VL logic tables.

This was what I actually meant to write. I think it doesn't invalidate the rest of my argument.

>> This proves that the suggestion to treat UNKNOWN and NULL
>> in a truth valued domain as being equal is not possible. (Yes, I have
>> seen people make that suggestion - I don't believe it was in this group,
>> though).
>
>I thought, based on a Date article, that this actually was the SQL
>standard (except that FALSE AND NULL/UNKNOWN is FALSE, thus breaking
>your foundation #1). But maybe I misread (or misremember) him; maybe he
>was wrong; maybe he was talking about a proposed standard that never
>came to be.

I had to check it. I mainly use SQL Server 2000, and that's mostly based on the SQL-92 standards, where there is no 3VL valued datatype, nor a boolean datatype.

But you're correct - in the SQL-99 standard, a BOOLEAN (sic!) datatype is introduced. According to the description in the standard, a boolean value is either True or False. "The truth value of _unknown_ is sometimes represented by the null value".

If a data type accomodates not only True and False but Unknown as well (represented in whatever way), I'd hesitate to call it Boolean, since most people associate the term Boolean with the most simple Boolean algebra, that has only True and False. But on the other hand, if all rules for a Boolean algebra are met, there's technically no reason to object to the name Boolean for this data type.

There is a reson to object to the use of "the null value" to represent the truth value Unknown. Elsewhere in the standard, the Null value is defined as "A special value that is used to indicate the absence of any data value". Of course, if Unknown is considered to be a data value for a boolean data type (and the rest of the standard clearly indicates it is), it should not be represented by the same symbol that is also used to represent the absence of a data value, since a data value can not be absent and present at the same time.

Further in the document, in the part where <boolean value expression> is detailed, the boolean truth tables are given. According to these tables, False AND Unknown should evaluate to False and True OR Unknown should evaluate to True. Makes sense. But since Unknown and NULL are considered equal in the context of a Boolean data type, this also means that False AND NULL evaluates to False and True OR NULL evaluates to True. The basic rule of NULL propagation is out of the window!!

I had read somewhere that the Boolean data type is no longer defined in the SQL-2003 standard. Based on the above, this doesn't come as a surprise :-) However, a quick search on internet brought me to a (probably draft) copy of the SQL-2003 standard that still does include the Boolean data type, exactly as it was in SQL-1999. Shame. :-((

>> But if we have to define seperate results for FALSE AND TRUE, FALSE AND
>> UNKNOWN, FALSE AND FALSE, and FALSE AND NULL, then we are clearly no
>> longer dealing with the original 3VL logic tables that are mentioned in
>> rule 3 above. Rather, we are extending them.
>
>Isn't it possible to say we are dealing with the normal 3VL tables
>(without NULL), but that they only apply when there are no NULLs? After
>all, NULL is not a value. Smells bad, perhaps, but I don't really see
>the difference compared to how other domains deals with NULL.

It's good that you see no difference compared to how other domains handle NULL, because there should not be any difference.

>
>> While this might theoratically and technically work, I don't like the
>> idea. There are already too many people flabbergasted by 3VL - what
>> would "4VL" do for them?
>
>No need to call it 4VL. NULL is not a value. How NULL "works" is easy to
>remember; it destroys everything it touches (I.e. all expressions
>involving NULL evaluate to NULL (exceptions excepted)). For the rest,
>you have the normal 3VL truth tables.

Removing the row and column for NULL from the truth tables and replacing them by a line that says that NULLs propagate changes only the representation of the tables, not the content. You're still dealing with logic that has to accomodate for four possibilities: the three values True, Unknown ald False, and the "special value" NULL.

>
>> For me, it's just too many problems for too little gain.
>
>Fair enough.

Best, Hugo

-- 

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