[ http://issues.apache.org/jira/browse/DERBY-402?page=all ]
Rick Hillegas updated DERBY-402:
--------------------------------
Derby Info: (was: [Patch Available])
> 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
