In Defense of Nulls (4)

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 :

ANSI SQL 89 also defines WHERE & HAVING in subqueries as returning only 2 possible logic values - True or False. WHERE & HAVING in subqueries should return 3 possible logic results - True, False or Unknown. WHERE & HAVING in subqueries have the same 'bug' as EXISTS.
Some RDBMS's routinely convert other subqueries to EXISTS subqueries during optimization. In some cases, the negated EXISTS used by Date can be generated from non-negated subqueries. For instance,
is converted to,
Note: there are other aspects to this conversion that are not shown here.
These RDBMS's may exhibit the EXISTS bug on other subqueries.

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,

SELECT * FROM ... WHERE <predicate> returns all rows for which <predicate> is True. All rows where <predicate> is False can be retrieved with,

SELECT * FROM ... WHERE NOT <predicate> The MAYBE operator allows retrieval of rows where <predicate> is Unknown, as follows,

SELECT * FROM ... WHERE MAYBE <predicate> 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