Re: empty string == NULL

In article <3867B012.E5B995FB_at_dced.state.ak.us>,   Calvin Crumrine
<Calvin_Crumrine_at_dced.state.ak.us> wrote:
> I don't understand the statement that there's no
way of knowing what an empty column contains.
> It seems obvious to me that it contains nothing,
although that leads to the English-statement
> paradox that nothing is something. (The paradox
may exist in other languages also, but it's
> still only a paradox due to language

limitations. Does 0 represent a quantity? Is black a
> color? I won't say that all paradoxes are due to
language limitations, but I suspect that most
> of them are.)

Sorry for my poor explanation before. Let me try again. The problem with NULL is, to quote Joe Celko in SQL for Smarties', "A NULL means that we have a missing, unknown, miscellaneous, or inapplicable value in the data."

If a column can have a value ranging from -100 to +100 and no value has been specified it is presumptuos and, depending on the application, could even be life threatening to assume a value of 0. Ridiculous extreme example -- picture a submarine with an emergency lifesaving system that opened the escape hatch when the reading for the pressure gauge is zero, but the gauge is malfunctioning and the programmer arbitrarily decided to use the value 0. The lifesaving system kicks in and the sub is hundreds of feet below the surface -- goodbye submarine and crew.

What do we do if the user has specified a real number where the column validation rules call for an integer value? Truncate, round, round up, substitue a zero, substitute the minimum value or maximum value allowed...

> Likewise, rows containing nulls in columns being
used to qualify candidates for selection
> should be treated the same as any other value,
i.e. returning FALSE or TRUE based on whether or
> not you want to select rows containing nulls in
those columns. This, as I understand it, is
> exactly how nulls are in fact handled, i.e.
simply as a separate and unique 'value'. It seems
> to me that the primary advantage of Null is that
it is typeless. That is, I can
>
> SELECT * FROM TABLEA WHERE COLUMNA = '' only
when COLUMNA is a character datatype but I can
> SELECT * FROM TABLEA WHERE COLUMNA IS NULL
regardless of COLUMNA's datatype.
>
> Since these statements are not synonymous per
Codd, it complicates the programming but it's
> really no different than ensuring that COLUMNA
is truncated when the user inserts nothing but
> spaces. (Imagine the problem selecting character
data if you don't truncate spaces! It's only
> one extra step to check the length after
truncating the spaces and change the 'value' to NULL
> when the length=0. That preserves the logic if
Oracle should ever change how it handles
> 0-length strings. Sorry about calling NULL a
value, but I can't think what else to call it.
> Non-value?)
>
> Jerry Gitomer wrote:
> >
> > The "trinary" logic of having True, False, and
Null goes back to Codd's
> > original article on relational databases. I
suspect that the only way it will
> > get changed is if it is explicitly banned in
the ANSI SQL standard.
> >
> > If I remember Codd rationale for NULL was that
there is no way of knowing what
> > an empty column contains and therefore rows
containing nulls in columns being
> > used to qualify candidates for selection
should be ignored.
> >
> > You can always use the NVL function to convert
NULL into whatever you feel is
> > appropriate -- and the fact that any two
developers might not agree as to what
> > is appropriate tends to prove that Codd's
theoretical approach is really quite
> > practical
> >
> > hth
> >
> > Andreas Steidle wrote:
> >
> > > From the oracle doc:
> > >
> > > Do not use null to represent a value of
zero, because they are not
> > > equivalent. (Oracle currently treats a
character value with a length of zero
> > > as null. ... [1])
> > >
> > > Is there a workround for this contardictory
semantik garbage? A string is a
> > > string, even if it's empty!!! Perhaps some
env-var??
> > > In other words: Is there a way to make
Oracle remember a '' and NOT convert
> > > it to NULL ???
> > >
> > > thanx, Andreas
> > >
> > > [1] However, this may not continue to be
true in future releases, and Oracle
> > > recommends that you do not treat empty
strings the same as NULLs.
> > >
> > > Hope this will happen very soon as default
behavoir.

 --
 Once I figured out how to spell DBA I became one

• Jerry Gitomer

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Dec 28 1999 - 20:25:33 CST