Re: 'null' values

In article <3jh5i5$k55_at_infoserv.rug.ac.be>, rvdbergh_at_egidius.rug.ac.be (Ria Vandenberghe) wrote:

>I am looking for information about (a) the internal representation of
>'null' values and (b) the treatment of 'null' values, in the major
>commercial relational DBMSs.
>
>(a) How are 'null' values *internally* represented in the DBMS ?
>e.g. by means of some reserved value in the range of possible values, or
>by means of some invalid value, for each type of field supported by the
>DBMS; by means of some (hidden) extra bit/byte; ....
>
>(b) As to the treatment of 'null' values, does the DBMS deviate from the
>SQL2 standard in any way ?
>
>
>Many thanks in advance for your replies,
>Regards,
>
>Ria.
>-----------------------------------------------------------
>Ria Vandenberghe Ria.Vandenberghe_at_rug.ac.be
>UNIVERSITY OF GHENT (BELGIUM) - Computer Science Laboratory
>Technologiepark-Zwijnaarde 9, B-9052 ZWIJNAARDE, BELGIUM
>tel: +32/9/264.55.10 fax: +32/9/264.58.42

this reply is from my personal experience, not detailed knowledge of oracle internals, but it appears that oracle represents nulls as zero-length character strings. the two objects (null and '') behave almost identically, the only difference being that you can't say things like "is not ''". BOTH of these objects are treated like nulls, e.g., you always get FALSE when you compare anything to a zero-length string, even *another* zero-length string.

i've always considered this a bug in oracle, and a very annoying one at that. even a zero-length string that results from an expression is treated as a null. i suspect that part of the reason they have never fixed it is that it's been that way so long. some people are undoubtably depending on it at this point.

i don't know if this deviates from the SQL2 standard or not. i vaguely recall that SQL2 makes no provision for zero-length strings, probaby because oracle was on the standards committee. :-(

despite my strong negative feelings about this bug, it's one of the few things that i find really annoying about oracle. in general i consider it an excellent sql engine. Received on Wed Mar 08 1995 - 23:21:46 CET