Re: Oracle NULL vs '' revisited

On Aug 18, 1:28 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor..._at_acm.org> wrote:
> "William Robertson" <williamr2..._at_googlemail.com> wrote in message
>
> news:1187428777.650700.87100_at_w3g2000hsg.googlegroups.com...
>
> > Because more than one rival product
> > is different to Oracle, Oracle gets called "non-standard". Is that the
> > basis for "Oracle doesn't adhere to SQL standards for the distinction"
> > or am I missing something?
>
> The basis for saying that "Oracle doesn't adhere to SQL standards" is the
> ANSI/ISO/IEC SQL standard document.
> ISO/IEC 9075-2:2003. Section 4.2.1:
>
> "A character string is a sequence of characters. All the characters in a
> character string are taken from a single character set. A character string
> has a length, which is the number of characters in the sequence. The length
> is 0 (zero) or a positive integer."
>
> --
> David Portas

Thanks - nobody ever managed to produce that quote during the course of the OTN debate and instead seemed to argue from what they were used to, then set theory, Codd and I think Aristotle were dragged in and things degenerated from there.

I think however the above definition oversimplifies things by not mentioning nulls. 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.

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. Received on Mon Aug 20 2007 - 04:58:00 CDT