Re: Oracle NULL vs '' revisited

On 20 Aug, 10:58, William Robertson <williamr2..._at_googlemail.com> wrote:
>
> I think however the above definition oversimplifies things by not
> mentioning nulls.

The SQL standard runs to more than 2000 pages and I think it has rarely been accused of oversimplifying things! Nulls are defined elsewhere.

> In the case where a missing value stands for
> "unknown", its length is also unknown and should be reported as null,
> not 0. (I would expect this also to be true in set theory when we
> don't know what's in the set.) So now we are asking how or whether we
> should distinguish between "unknown" and "definitely no value" in the
> case of strings but not apparently dates, and all the standard can
> tell us is that string lengths start at 0. Yes that implies that we
> can use zero-length strings for "definitely no value" values, and that
> implies that nulls should be implemented differently and that we
> should have two kinds of emptiness as SQL Server does and Oracle does
> not, but I wish it would come right out and say so.
>

I think you are missing the point. A zero-length string does not mean "definitely no value" and no such thing is implied. A zero-length string IS a value. Like all values it has the property of being equal to itself - a property that a null does not have because a null is not a value.

As is to be expected, the standard is completely silent on any userascribed  meaning for values. Nor does the standard generally make the mistake of saying that null is to be used to represent "unknown" (except in the unfortunate case of the Boolean type - but we don't need to deal with that in the present discussion).

> Oracle could of course implement this by (finally) changing the
> reserved VARCHAR type, or perhaps inventing a VARCHAR3, but I'm hoping
> that day will never come as things will get massively more complicated.
>

For those who use only the Oracle DBMS, yes. For those who require interoperability and portability things would be simplified.

--
David Portas