The Problem is Bigger than Date Thought!


In his article, 'Beware of SQL EXISTS', C. J. Date warns of flaws in ANSI SQL's definition of EXISTS. An extended version of the example he used in the article is described in the prior section, The EXISTS Flaw. This example shows that the ANSI SQL EXISTS operator can produce wrong results, but the problems in SQL EXISTS are symptomatic of a larger problem.

Actually, the wrong results in the example query are not produced by EXISTS but by the WHERE clause within the EXISTS subquery. ANSI SQL defines the WHERE clause as returning True or False, a 2 valued result rather than a 3 valued result including Unknown. In the subquery,

(SELECT *
 FROM   sp spy
 WHERE  spy.sno = spx.sno
 AND    spy.sno = 'P1'
 AND    spy.qty = 1000)
The final sub-expression (spy.qty = 1000) produces an Unknown result if spy.qty is null, as when the variable spy is (S1,P1,NULL). This causes the result of the WHERE expression to be Unknown. Since the WHERE clause in ANSI SQL cannot return Unknown, it is transformed to False, causing S1 to be incorrectly included in the result set. The error is caused by the action of the WHERE clause, not the EXISTS!

Some ramifications of the larger problem are:

The flaw in SQL subquery processing is a major problem for users of relational systems. Though this problem is widespread, there are solutions. The FirstSQL implementation of subqueries shows none of the errors described above. A live demonstration of FirstSQL's correct implementation using DATE's queries is provided in the next section, How FirstSQL Solves the EXISTS and Other Problems.

Return to Contents Page: DBMSs Process Subqueries Correctly?     Return to Issues Page


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