Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <1986678680.1140303025066.JavaMail.jira@ajax.apache.org>
Date: Sat, 18 Feb 2006 23:50:25 +0100 (CET)
From: "Jeff Levitt (JIRA)" <derby-dev@db.apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Updated: (DERBY-402) INTERSECT/EXCEPT/UNION operators don't
 agree with documented behavior.
In-Reply-To: <1077869268.1120003857390.JavaMail.jira@ajax.apache.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

     [ http://issues.apache.org/jira/browse/DERBY-402?page=all ]

Jeff Levitt updated DERBY-402:
------------------------------

    Attachment: derby402Final.diff
                rrefselectexpression.html
                rrefsqlj1083019.html

This patch needed to be updated since Eric Radzinski recently contributed a patch that modified one of the files in this patch.  As a result, this patch would have caused a conflict, so I deleted the old patch and am replacing it with a new one with the same changes except it was made at a higher revision number.  HTML files included for review.

> INTERSECT/EXCEPT/UNION operators don't agree with documented behavior.
> ----------------------------------------------------------------------
>
>          Key: DERBY-402
>          URL: http://issues.apache.org/jira/browse/DERBY-402
>      Project: Derby
>         Type: Bug
>   Components: Documentation
>     Versions: 10.1.1.0, 10.0.2.2
>     Reporter: A B
>      Fix For: 10.2.0.0
>  Attachments: 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