How FirstSQL Solves the EXISTS and Other Problems


Unlike most SQL based systems, FirstSQL does not give the wrong answers described by C. J. Date in 'Beware of SQL EXISTS'.

Date's EXISTS query is:

SELECT DISTINCT spx.sno
FROM   sp spx
WHERE  spx.pno = 'P1'
AND    NOT EXISTS
       ( SELECT spy.qty
         FROM   sp spy
         WHERE  spy.sno = spx.sno
         AND    spy.pno = 'P1'
         AND    spy.qty = 1000) ;
This query when run under FirstSQL against the Example Database produces the correct result of a single row - S2. Other systems incorrectly produce two rows (S1,S2) for this query because of incorrect EXISTS processing. WHERE processing is also incorrect. In other RDBMSs, both EXISTS and WHERE improperly convert Unknown logic results into False.

The qty of part P1 supplied is null (missing) for supplier S1. This causes the sub expression:

         spy.qty = 1000
to have an Unknown result. The WHERE converts this to False in other RDBMSs. The NOT before EXISTS converts False to True causing the response - It is true that supplier S1 definitely does supply part P1 and definitely does not supply it in quantity 1000. That result is incorrect. We do not definitely know that supplier S1 does not supply part P1 in quantity 1000; the quantity supplied is missing.

FirstSQL correctly processes this query because EXISTS and WHERE do not convert Unknown to False. In the example query, the NOT before EXISTS leaves the Unknown unchanged, and then supplier S1 is not included in the result set. This is correct. We know that S1 supplies part P1, but we don't know in what quantity. Supplier S1 should not be listed as a supplier who supplies P1 but definitely not in quantity 1000.

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


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