[jira] Commented: (DERBY-3951) Derby SQL Result Using 'Not Exists' & 'Except' Together Seem Incorrect

["Knut Anders Hatlen (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-3951?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12648165#action_12648165 ] 

Knut Anders Hatlen commented on DERBY-3951:
-------------------------------------------

The bug was fixed in this check-in:

------------------------------------------------------------------------
r532509 | abrown | 2007-04-26 00:26:40 +0200 (Thu, 26 Apr 2007) | 22 lines

DERBY-2370: Fix incorrect results for EXISTS queries with SET operator
expressions.  In particular:

  1. Adds the ability to mark a FromList as "transparent", and updates
     FromList.bindExpressions() to pass the outer FROM list down (instead
     of "this") if the FromList is transparent.
  2. Updates FromList.expandAll(...) to account for the fact that outer
     FROM tables might now appear in a nested FromList (as a result of
     "transparent" FromLists; see code comments for details).
  3. Modifies the "setResultToBooleanTrue()" signature to return a
     ResultSetNode (it was "void" previously).
  4. Modifies ResultSetNode.setResultToBooleanTrue() to always return "this".
  5. Modifies SetOperatorNode.setResultToBooleanTrue() so that it now creates
     an internal "SELECT *" query whose FROM list contains just the
     SetOperatorNode. Then we transform the "*" for the new SELECT into "TRUE"
     and leave the SetOperatorNode's result columns UN-transformed. Finally,
     mark the new SelectNode's FromList as "transparent" and return the new
     SelectNode. 
  6. Updates tests where necessary (including changed behavior in union.sql)
     and adds a new JUnit test, lang/ExistsWithSetOpsTest, to verify new
     behavior.

------------------------------------------------------------------------

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