Friday, March 11, 2011

Great Moments in History: Fun With NULLs

One of the reasons lots of people (like me) don't like SQL NULLs is because so many people (like me) have trouble with them.

Some folks, however, have more trouble than others. Like the original creators of Oracle who confused NULL with the empty string.

Well, why else would Oracle automatically convert '' to NULL?

Not just convert it, but define it as being the same as NULL. In other words, in Oracle the predicate '' IS NULL is true:



Oracle's not the only one. Sun's JDBC-to-ODBC bridge was unable to distinguish an SQL NULL string from empty string...



Not a bug

It must be a feature because...
This is not a bug. Some databases consider an empty string to be synonymous with NULL.
Never mind what ANSI says, what does ANSI know?
Misunderstanding of how Null works is the cause of a great number of errors in SQL code, both in ISO standard SQL statements and in the specific SQL dialects supported by real-world database management systems. These mistakes are usually the result of confusion between Null and either 0 (zero) or an empty string (a string value with a length of zero, represented in SQL as ''). Null is defined by the ISO SQL standard as different from both an empty string and the numerical value 0, however. While Null indicates the absence of any value, the empty string and numerical zero both represent actual values.
Maybe it's a good thing Oracle bought Sun, gather all the stupids bugs in one place so they're easier to avoid.

3 comments:

Anonymous said...

Great fun, indeed - if it hadn't these serious consequences of misunderstanding/messing up senseful concepts like NULL.

And a great final conclusion:)

Volker

alexeyk77 said...

As I know, Sybase ASE also interpret empty string as Nulls :(

Bill said...

when I was a novice with SQL (now I am an expert and know at least 10% of all the capabilities in SQL Anywhere...), nulls caused some confusion. Once I understood it, I have found nulls to be of great value! Long live nulls.