[
https://issues.apache.org/jira/browse/DERBY-3951?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Knut Anders Hatlen closed DERBY-3951.
-------------------------------------
Resolution: Duplicate
Closing the issue as a duplicate of DERBY-2370.
> 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
> Attachments: d3951.sql
>
>
> 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.
