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

From: Frank Hamersley <terabitemightbe_at_bigpond.com>
Date: Tue, 29 Nov 2005 13:21:12 GMT
Message-ID: <cBYif.6655$ea6.2147@news-server.bigpond.net.au>

David Cressey wrote:
> "Frank Hamersley" <terabitemightbe_at_bigpond.com> wrote in message
> news:A7wif.5513$ea6.729_at_news-server.bigpond.net.au...
>

>>It has always escaped me why Sybase particularly (and perhaps others)
>>convert '' to ' ' for varchar attributes.  I must admit I haven't
>>applied much brain power to rationalising the impact on the query engine
>>of unwinding this behaviour, but on first impression can't understand
>>why NULL, '' and ' ' are not distinguishable.  Curiously RTRIM('') gives
>>NULL as a result!
>>
>>Anyway, thats the way it is. Perhaps someone can turn on the light?

>
> The engineers are not infinite in their wisdom.
>
> The most robust solution for storing NULLS is a bit vector stored with each
> row, that indicates whether data is present or not present in each of the
> cells. However, this adds overhead. DEC Rdb did it this way. DEC's
> solution has the overhead, even in cases where a coulmn is declared NOT
> NULL.
>
> Oracle chose to indicate a null by the "impossible values" method. For
> example, A NULL in a NUMBER is represented by minus zero. A single zero
> byte is used to represent a NULL in a VARCHAR. Unfortunately, an empty
> string, having length zero, gets the same representation. Oops!
>
> I don't know about Sybase.

On zip research I am guessing the "impossible values" method given their propensity to force '' to ' ' to avoid embarassment and confusion.

Cheers, Frank. Received on Tue Nov 29 2005 - 07:21:12 CST