[ http://issues.apache.org/jira/browse/DERBY-402?page=all ]
Rick Hillegas updated DERBY-402: -------------------------------- Attachment: derby-402_v02.diff Attaching derby-402_v02.diff, which I committed at subversion revision 429484. This removes some odd characters which prevented the original patch from building. > INTERSECT/EXCEPT/UNION operators don't agree with documented behavior. > ---------------------------------------------------------------------- > > Key: DERBY-402 > URL: http://issues.apache.org/jira/browse/DERBY-402 > Project: Derby > Issue Type: Bug > Components: Documentation > Affects Versions: 10.1.1.0, 10.0.2.2 > Reporter: A B > Fix For: 10.2.0.0 > > Attachments: derby-402_v02.diff, derby402Final.diff, > rrefselectexpression.html, rrefsqlj1083019.html > > > I noticed the following two differences between what the documentation > (Reference Manual) says about UNION/INTERSECT/EXCEPT queries and what the > actual Derby behavior is. I'm filing this issue as a single "bug" against > Derby, but if it turns out later that this is really just a documentation > issue, or that these are "improvements" instead of bugs, anyone should feel > free to change the relevant fields in this issue... > 1) -- p. 63 of the Reference Manual (PDF version): "SelectExpression" -> > "Naming columns" > First paragraph in this section includes the following: > When the SelectExpression appears in a UNION, INTERSECT, > or EXCEPT operator, the names from the first SelectExpression > are taken as the names for the columns in the result of > the operation. > But this doesn't appear to be true. Ex: > ij> select a from t1 union select b from t2; > 1 // This "1" should be "A", according to doc. > ----------- > 1 > 2 > If this behavior is intentional, then an ORDER BY clause on a > UNION/INTERSECT/EXCEPT result set can only reference the result columns by > position (ex. would have to use "order by 1" in the above query since "order > by a" doesn't work (because the name of the result column isn't "a")). > Note that if both SelectExpressions have the same column name, then things > work differently: > ij> select a from t1 union select b as a from t2; > A // Now it's "A" instead of "1". > ----------- > 1 > 2 > So what needs to be corrected here? The documentation or the code? > 2) -- p. 133 of the Reference Manual: "Dynamic Parameters" -> "Where > dynamic parameters are allowed" > Number 16 says "a dynamic parameter is allowed to represent a column if it > appears in a UNION, INTERSECT, or EXCEPT expression." But the two examples > that it gives both fail: > ij> SELECT ? FROM t UNION SELECT 1 FROM t; > ERROR 42X34: There is a ? parameter in the select list. This is not allowed. > ij> VALUES 1 UNION VALUES ?; > ERROR 42X34: There is a ? parameter in the select list. This is not allowed. > I also tried preparing these statements using JDBC, but they failed there > with the same error.. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira