[
https://issues.apache.org/jira/browse/DERBY-4397?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12762908#action_12762908
]
Knut Anders Hatlen commented on DERBY-4397:
-------------------------------------------
Thanks for writing the spec, Dag. It generally looks good, but I have a couple
of questions:
1) If I read the syntax copied from the spec correctly, this query should be
allowed:
SELECT * FROM T1 ORDER BY X UNION SELECT * FROM T2 ORDER BY Y
With the suggested changes to the grammar, I think this query is not allowed
unless it's rewritten with parentheses around the sub-queries:
(SELECT * FROM T1 ORDER BY X) UNION (SELECT * FROM T2 ORDER BY Y)
2) Do you think this issue will address DERBY-4? If so, it would be good to
mention in the spec how INSERT INTO ... SELECT ... ORDER BY is supposed to
behave (in particular with respect to identity columns).
3) The spec mentions that ORDER BY in a sub-select can probably be ignored if
there's no windowing or offset/fetch on the same query level. I think that's
true (perhaps with the exception if INSERT INTO, see (2)). However, I think the
ORDER BY can be ignored even if there is a windowing clause, since the
windowing should be applied before the ORDER BY (see DERBY-4069) and would
therefore not guarantee that for instance row numbers have the same ordering as
the ORDER BY columns.
4) The spec says that a release note is needed. Are you aware of any
backwards-compatibility issues that require special mentioning, or is it
sufficient to put it in the list of new features?
> 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: 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.
