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

["Dag H. Wanvik (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DERBY-4397?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Dag H. Wanvik updated DERBY-4397:
---------------------------------

    Attachment: derby-4397-sortavoidance-a.stat
                derby-4397-sortavoidance-a.diff

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.