db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-4397) Allow ORDER BY in subqueries
Date Wed, 07 Oct 2009 07:14:31 GMT

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


Mime
View raw message