Derby SQL Result Using 'Not Exists' & 'Except' Together Seem Incorrect
-----------------------------------------------------------------------
Key: DERBY-3951
URL: https://issues.apache.org/jira/browse/DERBY-3951
Project: Derby
Issue Type: Bug
Components: SQL
Affects Versions: 10.2.2.0
Environment: Windows XP Version 2002 Professional Service Pack 2 /
Netbeans IDE 6.1 Build 200805300101 Derby 10.2.2.1 (538595)
Reporter: William Eimer
Priority: Minor
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
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
