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

On 10 Dec 2005 18:12:26 -0800, s.j.lagoe_at_googlemail.com wrote:

>Hello, I have 1 question about the 3VL that hopefully someone can
>answer. With data that we cannot avoid is missing we may have a
>true/false column that can have nulls in it which gives us a 3VL so
>that:
>
>((column_1=T) && (column_2=T)) = T
>((column_1=T) && (column_2=F)) = F
>((column_1=F) && (column_2=F)) = F
>((column_1=T) && (column_2=?)) = ?
>((column_1=F) && (column_2=?)) = ?
>((column_1=?) && (column_2=?)) = ?
>
>Now, I read in the thread that for this to be a sensible logic we have
>?=?. Ok, but that means that an unknown equals another unknown. That
>does not seem to make sense because by saying ? we at the start said
>that we dont know values... so how can we compare them at all? thank
>you, Sigmund.

Hi Sigmund,

The main problem, if I read your post correctly, is not in evaluating expressions using 3VL, but in the effects of STORING 3VL values.

The SQL-92 standard didn't allow this. It used 3VL only to evaluate predicates, but doesn't store True, False and Unknown.

This changed with SQL-1999. In this version of the standard, a Boolean datatype was introduced. I just posted a longer reply to Jon Heggland, elsewhere in this thread, that discusses why I think that this feature is as broken as can be. If you work with a database that supports the SQL-1999 or SQL-2003 Boolean datatype, I can only advise you not to use it.

If you really feel that you have to persist the result of evaluating a predicate in your database, you'll have to roll your own implementation and you'll have to make sure that it accomodates for not three but FOUR possible situations: the three truth values True, False and Unknown as well as the "missing value indicator" NULL.

Now, back to your question. If you have columns that can store the three truth values and NULL, then the result of a comparison between two of those values should be True if they are both True, both False, OR both Unknown, but the result should be Unknown if they ar both NULL.

You say that it doesn't seem to make sense that two Unknown's are equal to each other. I think that it makes just as much sense as saying that two True's are equal to each other. Just think what "True == True" actually means - the first True might be the result of "George W. Bush is currently president of the United States of America" the second might be the results of "I am currently not wearing my shoes". The predicates are totally unrelated - but you can still compare their respective truth values. They carry the same amount of truth - just as "Bill Clinton is currently president of the USA" and "I am currently asleep" carry the exact same amount of truth.

Best, Hugo

-- 

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