[jira] Commented: (DERBY-4397) Allow ORDER BY in subqueries

["Knut Anders Hatlen (JIRA)" <ji...@apache.org>]

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