db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "William Eimer (JIRA)" <j...@apache.org>
Subject [jira] Created: (DERBY-3951) Derby SQL Result Using 'Not Exists' & 'Except' Together Seem Incorrect
Date Sun, 16 Nov 2008 20:30:47 GMT
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.


Mime
View raw message