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: <42C07352.5010703@sbcglobal.net>
Date: Mon, 27 Jun 2005 14:44:50 -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: Re: Documentation vs. Actual behavior with UNION/INTERSECT/EXCEPT
References: <42C070B4.3050408@sbcglobal.net>
In-Reply-To: <42C070B4.3050408@sbcglobal.net>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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