Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Thu, 6 Dec 2012 12:26:59 +0000 (UTC)
From: "Dag H. Wanvik (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: <JIRA.12618971.1354751684896.165.1354796819557@arcas>
In-Reply-To: <JIRA.12618971.1354751684896@arcas>
References: <JIRA.12618971.1354751684896@arcas>
Subject: [jira] [Updated] (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


     [ https://issues.apache.org/jira/browse/DERBY-6008?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Dag H. Wanvik updated DERBY-6008:
---------------------------------

    Attachment: derby-6008-b.stat
                derby-6008-b.diff

Uploaded a slightly improved version of the patch, "*-b", (reintroduced a sane assertion I first removed erroneously).

Patch details:

Patch details:

M       java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj

Extends the production "nonJoinQueryPrimary" to allow "orderByClause" and "offsetFetchFirstClause".
If we see one ore more of the above and the query expression is a SELECT (i.e. not a VALUES clause), we push the clauses into the SelectNode, else give an error message.

M       java/engine/org/apache/derby/impl/sql/compile/SelectNode.java

Up till now, the clauses haven't been pushed down to SelectNode after binding; being collected by other node types, e.g. CursorNode and InsertNode, so we add logic to SelectNode to cater for the fact we now need to make binding happen of the clauses in that node type, too. Additionally, we do the shenanigans of hoisting up order by columns into the select if not already there, and remove duplicates after binding. 

M       java/engine/org/apache/derby/impl/sql/compile/SetOperatorNode.java

Any added hoisted order by column to the operand select node must be removed from the copied list when we build the result column list for thopse nodes.

M       java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java

Extended two comments to reflect new reality.

M       java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndOffsetFetchInSubqueries.java

Added test fixture testNestingInsideSetOperation.

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