Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
Received-SPF: neutral (asf.osuosl.org: local policy)
Message-ID: <42C070B4.3050408@sbcglobal.net>
Date: Mon, 27 Jun 2005 14:33:40 -0700
From: Army <qozinx@sbcglobal.net>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
 rv:1.7.1) Gecko/20040707
MIME-Version: 1.0
To: Derby Development <derby-dev@db.apache.org>
Subject: Documentation vs. Actual behavior with UNION/INTERSECT/EXCEPT
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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