Re: Why are empty VARCHAR2S always NULL?

From: <ctcgag_at_hotmail.com>
Date: 21 Aug 2002 17:38:38 GMT
Message-ID: <20020821133838.353$Ug@newsreader.com>

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote:
> "Wolfram Roesler" <wr_at_grp.de> wrote in message
> news:Xns927195BD69F7Awrgrpde_at_62.153.159.134...
> > "Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> wrote in
> > news:ajvnht$rq8$1_at_babylon.agtel.net:
> >
> > > This is not a bug, this is design decision taken by Oracle long time
> > > ago and it is unlikely to change. You have to live with it - empty
> > > varchar2 in Oracle was NULL, is NULL and probably will be NULL for
> > > the life of Oracle database software.
> > >
> >
> > Is there any wisdom behind that design decision, or is it merely a
> > bug that had to be declared a feature for compatibility reasons?
> > I find it very counter-intuitive that, for example, ''='' and
> > ''!='X' are false.

>

> Maybe I'm just sheltered but I can't see any reason why anyone would
> regard using the absence of any entry as signifying NULL a bug.

Because Null means unknown, while the empty string is known to be the empty string.

> What
> representation would you use for unknown.

Null.

> As for counter-intuitive well I sort of agree. Thats because we've got so
> used to TRU/FALSE logic that tri-value logic throws us. If you think of
> NULL as an unknown vale

Unfortunately, I can't think of NULL as an unknown value. Do to Oracle's implementation, I have to think of NULL as either an unknown value or a known to-be empty string, with no way to distinguish them.

> then you can rewrite your two comparisons as

>

> 1. Is this unknown value equal to this second unknown value? Clearly this
> can't be said to be either true or false but unknown.

See, that's just the point. I don't want to ask if this unknown value is equal to this second unknown value, and want to ask if two known strings are equal to each other.

> 2. Is this unknown value something other than 'X'? Again how can I
> possibly say true or false to this.

>

> >
> > How do I join by an empty string, for instance? Consider:
>
> What you mean is how do I join two unknown values.

No, what he means is 'how do I join by an empty string?'. Why do you find this so hard to believe?

> This is a meaningless
> join.

Only due to a poor implementation decision.

I have a great idea, lets make numeric zero also be null. It worked for the Romans.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
                    Usenet Newsgroup Service

Received on Wed Aug 21 2002 - 12:38:38 CDT