In Defense of Nulls


SQL Problems with 3-Valued Logic

The support for 3-valued logic in ANSI SQL 89 has several weaknesses. Most notable is the 'EXISTS bug' documented by C. J. Date in the September, 1989 Database Programming & Design. Date examines the results of 2 SQL queries - one using a negated EXISTS subquery and the other using a negated IN subquery.

These 2 queries should return identical results, but because of an incorrect definition of EXISTS in ANSI SQL 89, the EXISTS formulation returns incorrect results when nulls are present. ANSI SQL 89 defines EXISTS as returning only 2 possible values - True or False. EXISTS should return 3 possible values - True, False or Unknown. This is the EXISTS bug.

Actually, the SQL problem is larger than Date indicates, for two reasons :

The second weakness in SQL is the lack of an explicit operator to test for Unknown, sometimes called the MAYBE operator. This operator has the same syntax as NOT, taking a single operand. It returns True if the logic value of its operand is Unknown and False otherwise (when the operand is True or False). The MAYBE operator (or its equivalent) is essential to 3-valued logic, ostensibly supported by SQL.

The SQL query,

returns all rows for which <predicate> is True. All rows where <predicate> is False can be retrieved with, The MAYBE operator allows retrieval of rows where <predicate> is Unknown, as follows, These 3 queries will retrieve all possible rows in three non-overlapping sets.

There are RDBMS's supporting SQL that don't have these weaknesses. The FirstSQL DBMS does not have the EXISTS (or other subquery) bug. It correctly produces 3 possible logic values for EXISTS and other subqueries. FirstSQL also supports the MAYBE operator for properly detecting Unknown logic results. The MAYBE operator can be used anywhere that NOT can be used, including within constructs - MAYBE BETWEEN, MAYBE IN, MAYBE LIKE.

These problems with SQL should be corrected in all RDBMS's, otherwise Date's predictions may come true - planes will fall, bridges will collapse, etc.. Even SQL RDBMS's not supporting ANSI SQL may need repair.

SQL-92 does support a form of MAYBE - IS UNKNOWN. MAYBE can be transformed into SQL-92 as follows,

Unfortunately, SQL-92 still defines EXISTS, WHERE, HAVING incorrectly. SQL-92 has the EXISTS bug!

In the next section, various schemes for Default Values are discussed.

Return to Contents Page: In Defense of Nulls     Return to Issues Page


Copyright © 1998 FFE Software, Inc. All Rights Reserved WorldWide