Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <2105460242.1259695880826.JavaMail.jira@brutus>
Date: Tue, 1 Dec 2009 19:31:20 +0000 (UTC)
From: "Dag H. Wanvik (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Updated: (DERBY-4397) Allow ORDER BY in subqueries
In-Reply-To: <116396587.1254787171373.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


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

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

    Attachment: derby-4397-1.stat
                derby-4397-1.diff

Uploading a tentatively complete version of this patch,
derby-4397-1. After DERBY-4442 and the other cleanups in the INSERT
area went in, this patch now also solves DERBY-4, including the
expected ordering of identity columns.

Additionally, the INSERT cleanup has made it possible to simplify the
patch is several places compared with the earlier versions. :)

When this patch goes in, we should be ready to start work on allowing
FETCH/OFFSET in subqueries as well. Please review.


Patch details:

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

Wire in syntax for ORDER BY, cf. the specification document.

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

Add code to pull up and bind ORDER BY on a "from" subquery and finally
push it down to underlying result set as part of preprocess.

Change a call to RCL.size to be RCL.visibleSize, to account for the fact
that there may now be extra columns pulled up due to ORDER BY.

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

Add code to pull up and bind ORDER BY on a from subquery and finally
push it down to result set as part of preprocess. 

We also forbid flattening of a subquery if we have an ORDER BY on it.

The parser transiently uses SubqueryNode before replacing it with a
FromSubquery node so I added a getter method to retrive the order by
list to be used in that replacement.

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

Add code to pull up and bind ORDER BY on a from subquery and finally
push it down to result set as part of preprocess. For the push, see
also comments for NormalizeResultSetNode and ProjectRestrictNode.

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

Add logic to fetch the ORDER BY list when we parse parse a view text.
Next, hand it on to the fromSubquery being constructed for the view.


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

Replace call to size with visibleSize, see above. Add default
implementation of new RSN method setOrderBy needed by parser for
primaries to receive an ORDER BY list.

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

Adds logic in the generate method to poke the order by list's result
set number into OrderByNode's result set number.
We need to take note of result set number if ORDER BY is used in a
subquery for the case where a PRN is inserted in top of the select's
PRN to project away a sort column that is not part of the select
list, e.g.

    select * from (select i from t order by j desc) s

If the resultSetNumber is not correctly set in our resultColumns,
code generation for the PRN above us will fail when calling
resultColumns.generateCore -> VCN.generateExpression, cf. the Sanity
assert in VCN.generateExpression on sourceResultSetNumber >= 0.

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

Let flattenableInFromSubquery return false if we have an ORDER BY on a
subquery select. Also modify the way the PRN is added if we have an
ORDER BY on a subquery, so that references into the Selects RCL (made
from above us in the query tree) will not be voided by our adding a
PRN. The method is the same as used in other instance of this phase
problem: reuse the same RCL for the PRN and make a new one for the
select node, cf. for example DERBY-4450.

M      java/engine/org/apache/derby/impl/sql/compile/NormalizeResultSetNode.java
M      java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java

Add pushOrderBylist to push through to underlying result set, so it
will work when pushed from InsertNode, which sometimes may have an
intervening NormalizeResultSetNode and/or ProjectRestrictNode node
over the real result set to be ordered when we get to preprocess time.

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

Add code to remember generated result set number, see changes comments
for OrderByNode.

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

Add code to hold an ORDER BY list for a view query.

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

Replace instances of size with visibleSize, see above.

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

Trivial fix to printSubNode to make it safer for use in intermediate
stages of processing.

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

Added a missing printSubNodes and made a method static.

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

Added a debug utility method, stackPrint which prints current run-time
stack trace on derby.log. Yes, I know, not really part of this patch,
but I was too lazy to make a separate issue for it.. feel free to kick
me.

A      java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByInSubqueries.java

Tests for this feature.

M      java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql
M      java/testing/org/apache/derbyTesting/functionTests/master/orderby.out

Make negative tests positive (order by in subqueries now allowed).


> Allow ORDER BY in subqueries
> ----------------------------
>
>                 Key: DERBY-4397
>                 URL: https://issues.apache.org/jira/browse/DERBY-4397
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>            Reporter: Dag H. Wanvik
>         Attachments: derby-4397-1.diff, derby-4397-1.stat, derby-4397-all-subqueries.diff, derby-4397-all-subqueries.stat, derby-4397-insert-from-exists.diff, derby-4397-insert-from-exists.stat, orderBySpec.html, orderBySpec.html, orderBySpec.html, orderBySpec.html
>
>
> SQL 2008 allows ORDER BY to be specified in subqueries. In conjunction with OFFSET/FETCH and/or ROW_NUMBER
> meaningful subqueries with row ordering may be formulated. Cf. MySQL's LIMIT may be used in subqueries as well.
> Note that OFFSET/FETCH is currently not allowed in subqueries, either.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.