Return-Path: 
 <derby-dev-return-99468-apmail-db-derby-dev-archive=db.apache.org@db.apache.org>
X-Original-To: apmail-db-derby-dev-archive@www.apache.org
Delivered-To: apmail-db-derby-dev-archive@www.apache.org
Received: from mail.apache.org (hermes.apache.org [140.211.11.3])
	by minotaur.apache.org (Postfix) with SMTP id 20308E6F7
	for <apmail-db-derby-dev-archive@www.apache.org>;
 Fri,  7 Dec 2012 10:25:24 +0000 (UTC)
Received: (qmail 10606 invoked by uid 500); 7 Dec 2012 10:25:24 -0000
Delivered-To: apmail-db-derby-dev-archive@db.apache.org
Received: (qmail 10397 invoked by uid 500); 7 Dec 2012 10:25:23 -0000
Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:derby-dev-help@db.apache.org>
List-Unsubscribe: <mailto:derby-dev-unsubscribe@db.apache.org>
List-Post: <mailto:derby-dev@db.apache.org>
List-Id: <derby-dev.db.apache.org>
Reply-To: <derby-dev@db.apache.org>
Delivered-To: mailing list derby-dev@db.apache.org
Received: (qmail 10248 invoked by uid 99); 7 Dec 2012 10:25:21 -0000
Received: from arcas.apache.org (HELO arcas.apache.org) (140.211.11.28)
    by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 07 Dec 2012 10:25:21 +0000
Date: Fri, 7 Dec 2012 10:25:21 +0000 (UTC)
From: "Knut Anders Hatlen (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: <JIRA.12618971.1354751684896.2656.1354875921395@arcas>
In-Reply-To: <JIRA.12618971.1354751684896@arcas>
References: <JIRA.12618971.1354751684896@arcas>
Subject: [jira] [Commented] (DERBY-6008) Allow ORDER BY and FETCH/OFFSET in
 set operands
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394


    [ https://issues.apache.org/jira/browse/DERBY-6008?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13526286#comment-13526286 ] 

Knut Anders Hatlen commented on DERBY-6008:
-------------------------------------------

> Note the boolean flag "dontRemoveOrderingColumns" which guards against removal when the node isn't really a UNION but represents the top level for a VALUES clause with an ORDER BY.

I was wondering about that... When a VALUES clause has an ORDER BY, it can only reference one of the columns that will actually be returned:

ij> values 1,2 order by 1+2;
ERROR 42878: The ORDER BY clause of a SELECT UNION statement only supports unqualified column references and column position numbers. Other expressions are not currently supported. (errorCode = 30000)

So are there ever any generated ordering columns to remove in a VALUES clause? And if not, would it be harmful to call removeOrderByColumn() just to reduce the number of special cases?

I found this example, though:

ij> values 1 order by 1+2;
1          |2          
-----------------------
1          |3          

1 row selected

Not sure why it doesn't raise the same syntax error as the first query. But at least it looks wrong that the ordering column is part of the result.

I have logged DERBY-6009 for this existing bug.
                
> Allow ORDER BY and FETCH/OFFSET in set operands
> -----------------------------------------------
>
>                 Key: DERBY-6008
>                 URL: https://issues.apache.org/jira/browse/DERBY-6008
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>            Reporter: Dag H. Wanvik
>            Assignee: Dag H. Wanvik
>         Attachments: derby-6008-a.diff, derby-6008-a.stat, derby-6008-b.diff, derby-6008-b.stat, derby-6008-c.diff, derby-6008-c.stat
>
>
> Currently, Derby doesn't allow ORDER BY nested in a set operand, e.g. in the following construct:
> (select i from t1 order by j offset 1 row)    union 
> (select i from t2 order by j desc offset 2 rows)
> This is allowed by the standard, as far as I can understand, cf. this quote from section 7.12 in SQL 2011:
> <query expression body> ::=
>     <query term>
> |   <query expression body> UNION [ ALL | DISTINCT ]
>   [ <corresponding spec> ] <query term>
> |   <query expression body> EXCEPT [ ALL | DISTINCT ]
>   [ <corresponding spec> ] <query term>
> <query term> ::=
>    <query primary>
> |  <query term> INTERSECT [ ALL | DISTINCT ]
>    [ <corresponding spec> ] <query primary>
> <query primary> ::=
>    <simple table>
>   |  <left paren> <query expression body>
>      [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ] <right paren>
> I.e. the left paren chooses the second alternative in the production for <query primary>.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira