db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <j...@apache.org>
Subject [jira] Issue Comment Edited: (DERBY-4397) Allow ORDER BY in subqueries
Date Wed, 13 Jan 2010 12:46:54 GMT

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


Mime
View raw message