[
https://issues.apache.org/jira/browse/DERBY-4397?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12799722#action_12799722
]
Dag H. Wanvik edited comment on DERBY-4397 at 1/13/10 12:45 PM:
----------------------------------------------------------------
Uploading derby-4397-sortavoidance-a, a patch which makes sort avoidance work
in the case described above, using solution b, which passed regressions. A test
case is added to verify this
(OrderByAndOffsetFetchInSubqueries#testSelectSubqueriesSortAvoidance).
It does not yet avoid sorting in the case,
select * from (select i from t order by i) t order by i
presumably because the outer order by doesn't make use of the fact that the
subquery is already sorted on that column. Without the inner "order by", the
subquery is flattened and no sorting is performed. Is the flattening a
prerequisite for the optimizer to handle such cases?
The query
select * from (select i from t offset 0 rows) t order by i
currently stops flattening from happening, and it also incurs an unneccecary
sort (there is an index on i here)
was (Author: dagw):
Uploading derby-4397-sortavoidance-a, a patch which makes sort avoidance
work in the case described above, using solution b, which passed regressions. A
test case is added to verify this
(OrderByAndOffsetFetchInSubqueries#testSelectSubqueriesSortAvoidance).
It does not yet avoid sorting in the case,
select * from (select i from t order by i) t order by i
presumably because the outer order by doesn't make use of the fact that the
subquery is already sorted on that column. Without the inner "order by", the
subquery is flattened and no sorting is performed. Is the flattening a
prerequisite for the optimizer to handle such cases?
The query
select * from (select i from t offset 0 rows) t order by i
currently stops flattening from happening, and it also incurs an unneccecary
sort.
> 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, derby-4397-sortavoidance-a.diff,
> derby-4397-sortavoidance-a.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.
