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 = 1000to 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