[
https://issues.apache.org/jira/browse/DERBY-4397?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12798386#action_12798386
]
Dag H. Wanvik commented on DERBY-4397:
--------------------------------------
The reason we see a VirtualColumnNode in the OrderByColumn is that FromSubquery
changes the result column list of its subquery *after* we have bound the order
by list, cf. this comment in the code:
/* Now that we've bound the expressions in the subquery, we
* can propagate the subquery's RCL up to the FromSubquery.
* Get the subquery's RCL, assign shallow copy back to
* it and create new VirtualColumnNodes for the original's
* ResultColumn.expressions.
:
*/
subquery.setResultColumns(subqueryRCL.copyListAndObjects());
subqueryRCL.genVirtualColumnNodes(subquery, subquery.getResultColumns());
resultColumns = subqueryRCL;
that is, the result column list that was used when binding the order by list
has now been moved up one level and been given an additional level of VCNs.
I find I can avoid this problem by either:
a) moving the binding of the order by list til after this step, or
b) construct a new RCL for the FromSubquery and leave the old one alone.
a) is the safer option; I don't know if b) will have any negative side effects,
but will run the regressions to see if anything pops. A priori I would prefer
b) since it seems a more direct solution.
> 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
> Assignee: Dag H. Wanvik
> Attachments: derby-4397-1.diff, derby-4397-1.stat, derby-4397-2.diff,
> derby-4397-2.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, 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.
