db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] Closed: (DERBY-3951) Derby SQL Result Using 'Not Exists' & 'Except' Together Seem Incorrect
Date Mon, 17 Nov 2008 14:05:44 GMT

     [ 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.


Mime
View raw message