[DERBY-3951] Derby SQL Result Using 'Not Exists' & 'Except' Together Seem Incorrect

• Type: Bug
• Status: Closed
• Priority: Minor
• Resolution: Duplicate
• Affects Version/s: 10.2.2.0
• Component/s: SQL
• Environment: Windows XP Version 2002 Professional Service Pack 2 / Netbeans IDE 6.1 Build 200805300101 Derby 10.2.2.1 (538595)
• Urgency: Normal
• Bug behavior facts: Wrong query result
• Assignee: Unassigned
• Reporter: Markus Weimer
• Created: 16/Nov/08 20:30
• Updated: 17/Nov/08 14:04
• Resolved: 17/Nov/08 14:04

Description

Derby seems to produce a different result than Oracle does, for what seems to be an equivalent query. It seems like a bug in that the Oracle equivalent gives the correct result, and the Derby equivalent does not.

Both the Derby 'Not Exists' clause and 'Except' clause seem to work correctly separately. However, when combined the use of the 'Not Exists' clause working with the 'Except' clause seems to render an incorrect result. (Oracle uses the Minus clause, instead of the Except clause, otherwise they are the same.) Here is the situation:

(The example below refers to a textbook example of Sailors Reserving Boats.)

– Query with Correct Result, by both Derby and Oracle
– (To see sailors that have reserved all boats.)
SELECT S.sname
FROM Sailors S
WHERE NOT EXISTS
(SELECT B.bid
FROM Boats B
WHERE NOT EXISTS
(SELECT R.bid
FROM Reserves R
WHERE R.bid = B.bid and R.sid = S.sid));
– Correct Result:
– Dustin –

The below Query works in Oracle (with Minus), but does not provide the correct result in Derby (with Except).
SELECT S.sname
FROM Sailors S
WHERE NOT EXISTS
((SELECT B.bid
FROM Boats B)
EXCEPT
(SELECT R.bid
FROM Reserves R
WHERE R.sid = S.sid));
--Oracle Result (using MINUS):
– Dustin
–
-- Derby Result (as above, using EXCEPT):
– Dustin
– Lubber
– Horatio
– Horatio
– Fred

Below are the tables with related data for above examples:
(Sailors Reserve Boats. Looking at the Reserves tables is it easy to see
that only one sailor -SID 22 Dustin - has reserved all Boats.)

SAILORS table
SID,SNAME,RATING,AGE
22,Dustin,7,45
29,Brutus,1,33
31,Lubber,8,55.5
32,Andy,8,25.5
58,Rusty,10,35
64,Horataio,7,35
71,Zorba,10,16
74.Horataio,9,35
85,Art,3,25.5
95,Bob,3,63.6
131,Fred,8,55.5

BOATS table
BID,BNAME,COLOR
101,interlake,blue
102,interlake,red
103,Clipper,green
104,Marine,red

RESERVES table
SID,BID,DAY
22,101,Oct 10,1998
22,102,Oct 10,1998
22,103,Oct 8,1998
22,104,Oct 7,1998
31,102,Nov 10,1998
31,103,Nov 6,1998
31,104,Nov 12,1998
64,101,Sep 5,1998
64,102,Sep 8,1998
74,103,Sep 8,1998
74,103,Dec 8,1998
131,101,Oct 8,1998

Issue Links

• duplicates: DERBY-2370 EXISTS may return the wrong value for sub-queries involving set operations