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

On 22 Nov 2005 15:34:44 -0800, michael_at_preece.net wrote:

>Oh dear... I *really* didn't want to have to re-enter the fray here,
>but when your words have been taken out of context...

Hi Mike,

I can be clear on this: it was never my intention to take your words out of context. When I snipped text, I tried to find the perfect balance between keeping enough context to maintain the quotes as they were intended and limiting the amount of repeated text.

If you feel that my snipping changed the intentions of my words, then please accept my apologies.

I'll refrain from snipping in this message. Apologies this time to those who dislike long quotebacks.

>
>Hugo Kornelis wrote:
>
>> On 20 Nov 2005 19:58:44 -0800, michael_at_preece.net wrote:
>>
>> >
>> >Julian M wrote:
>> >
>> (snip)
>> >
>> >> NULL in SQL can seem tricky, but it is simple enough if viewed in terms
>> >> of what is intended by the ANSI SQL92/99 standards.
>> >> See drafts:
>> >> http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
>> >> http://www.ncb.ernet.in/education/modules/dbms/SQL99/
>> >>
>> >
>> >Here's your problem...
>> >
>> >"A special value that is used to indicate the absence of any data
>> >value."
>> >
>> >That's it right there. It doesn't make sense. The special value has to
>> >be present doesn't it? So something is present. What is it? Is it an
>> >indication that whatever it is is absent? Nope. If something is present
>> >it's present. If it's absent it's absent.
>
><UNSNIP>
>This only makes sense if it
>is interpreted to mean that it, whatever it is, is empty, or void, of
>any value.

You're still trying to put more meaning into it than NULL actually has. The only thing that NULL means is "there's no value here". Period. I'm sorry if that doesn't make sense to you - but that's just the way it is.

> Absence is the problem word. If I want something to be
>absent I'll delete it from the database.

Maybe this scratches the reason why you can't see the sense in NULL: you try to view SQL's NULL from a Pick mindset. In Pick, you can (if I understood the explanations correctly) delete a single cell (if "cell" is the correct term in a Pick context). That is not possible in an SQL table, which has to be rectangular: you can delete an entire row or drop an entire column, but you can't delete a single cell. Setting a NULL value in a column in an SQL table is equivalent to deleting a cell in a Pick database.

> If I want to record that
>something does not have any data value I'll record a null.

Now you are losing me. Can you give me a concrete example of something that is present without data value?

> Pick doesn't
>
>need a special value to indicate this presence of a lack of any value -
>
>it just uses a lack of any value. I don't have a problem with SQL
>implementations using a special value if they're intrinsically unable
>to record the lack of any value as a lack of any value.

I won't comment this part.

It is clear that you think Pick is (at least in this respect) superior to SQL. Fine - everyone's entitled to their opinions.

It is also clear that you didn't really want to be educated about how NULL in SQL should be interpreted, as you tried us believe in your first message - you merely wanted to "prove" that SQL-relational is "ridiculous".

I am not for one second prepared to choose sides in what you seem to consider a holy war of DBs. Flat-file, hierarchic, network, pick, relational - they all have their strenghts and weaknesses. Why not accept the differences, try to learn from each other, and remember how many tools we have to choose from to get a task done - the more we know about the various tools, the more chance of choosing the right one.

There is no single solution that serves all needs.

>Also...
>
>"A value of the Boolean data type is either true or false. The truth
>value of unknown is sometimes represented by the null value."
>
>Dodgy or what?! It would be better to use a large cumbersome robot
>waving its arms about saying "does not compute" than to use null for
>this.

I snipped this out of my previous reply because I felt no need to react. You are correct - this is dodgy. If you google for it in the newsgroup microsoft.public.sqlserver.programming, you might find a thread (a few months back) where I defend the choice (in SQL-92) to NOT allow a boolean datatype. As soon as you do allow it, you are forced to destroy at least one of the basic rules of SQL-relational.

The quote above is probably from the SQL-99 standard. Apparently, pressure from the paying customers who just wanted to store a boolean in their tables without all that hassle about theoretical correctness won from those who tried to keep at least some relational theory intact in SQL. The results, as you see, is dodgy. And that's a euphemism.

>
>Mike.
>
>PS. That's definitely it for me on this thread. Thanks and so long.
></UNSNIP>
>
>Note the use of CAPS on the UNSNIP to emphasize the deliberate attempt
>at obfuscation.

No "deliberate attempt". See the opening paragraph of this message.

> You make a great point about the fact that a special
>value is used, when I do, in fact, go on to acknowledge that.

What I made a point about is the distinction between a _data_ value on the one hand, and a _special_ value on the other hand. A special value is NOT a data value.

You said it didn;t make sense to have a marker to denote the absence of a data value, since there IS a value there. Yes, there is indeed. But it's a special value, not a data value. So it makes perfect sense after all.

>
>>
>> Hi Mike,
>>
>> Pay more attention to what you read before you reply.
>
>Oh the irony! I'll be keeping a keen eye out for what you snip and what
>you leave in your posts in future. It seems your snippage in this case
>was motivated by a need to "win" the argument - without regard to
>accuracy and completeness. Maybe it was designed for just this
>eventuallity - that I would be forced to respond...
>
>> I'll use caps to
>
>...try to win the argument if twisted logic fails

Oh dear. No, Mike, I'm not trying to win an argument. In fact, I'm trying very hard to stay out of the argument.

As I already stated in several messages, your first post in this thread gave me the impression that you sincerely wanted to be helped to figure out how NULLs in SQL work. Things like "I've been accustomed to thinking ...", "Now, as I read up on SQL, I find that ...", a number of questions and "Sorry - head is spinning." gave me that impression. I thought I could help clarify things; that's why I replied.

Of course, if your first post had already contained the texts "Poor fools. Poor deluded fools. The sooner SQL-relational is seen for what it is - ridiculous - the better." and "Strong enough you reckon?", I would have known better than to reply. But unfortunately, you were clever enough to withhold those comments until later.

>
>[remainder snipped as it was a distortion of the points I was making]
>
>Mike.
>
>PS. To put this as clearly as I can :-
>
>The presence of the special value is necessary (in some DBMSs) to
>indicate a fact. The fact is that we have no value. Unknown, on the
>other hand, equates to an absence of any facts. Can you see that
>"unknown" is therefore not equal to "no value"?

This is what I've been saying in all my messages: NULL is not the same as unknown. NULL is a marker for an absent value. Unknown might be the reason why the value is absent, but it need not be - only someone with knowledge of the domain can judge whether a particular NULL in a particular column of a particular table can be interpreted as unknown.

> If not, I would be
>happy to expound on this. The standard would have been better had it
>defined NULL as "No value. An empty set. A special value may be used to
>indicate its presence.".

No. NULL is not "no value"; it is a value - "a special value".

NULL is also not an empty set. It is a scalar. Sets and scalars can't be swapped around at will in SQL.

There is no need for a special value to indicate the presence of NULL, since NULL itself is already a special value.

>
>Regarding the use of NULL to record a boolean truth of unknown... The
>fact is, in this case, that we know something is neither true nor
>false. Whether that is because it is inapplicable, or simply because we
>have insufficient data, or for any other reason, we do know something -
>even if it's only in terms of what it is not equal to. I really don't
>think it is at all wise to confuse this fact (that something is neither
>true nor false) with having no value. I have no recommendation as to
>how this should be handled - other than to suggest it is a bad mistake
>to use NULL for it. Perhaps it just depends.

Agreed. That's one of the many points I raised in the discussion in another group I refered to above. Using NULL to represent the boolean value Unknown is a big mistake. I understand that you have no recommendation how to handle it, as (IMHO) there IS no way to handle it, other than to disallow the addition of a boolean datatype in SQL.

Best, Hugo

-- 

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