db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Army <qoz...@sbcglobal.net>
Subject Re: Documentation vs. Actual behavior with UNION/INTERSECT/EXCEPT
Date Mon, 27 Jun 2005 21:44:50 GMT
Ooops, copy-paste error.

 > ij> select a from t1 union select b as a from t2;
 > I                // Now it's "A" instead of "1".
 > -----------
 > 1
 > 2

The "I" result column name above is actually "A", I just copied from the wrong 
window.  Sorry for any confusion.

Army

--

Army wrote:
> 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 not sure if the documentation is 
> wrong or if the behavior is wrong--anyone out there know?
> 
> 1) -- p. 63: "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;
> I                // Now it's "A" instead of "1".
> -----------
> 1
> 2
> 
> So what needs to be corrected here?  The documentation or the code?
> 
> 2) -- p. 133: "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.  I assume this is just a documentation error, 
> but if anyone knows differently, please speak up...
> 
> Thanks,
> Army
> 
> 
> 


Mime
View raw message