db-derby-dev mailing list archives

[jira] [Commented] (DERBY-6008) Allow ORDER BY and FETCH/OFFSET in set operands

Date

Fri, 07 Dec 2012 02:31:21 GMT

[ https://issues.apache.org/jira/browse/DERBY-6008?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13526091#comment-13526091
]
Bryan Pendleton commented on DERBY-6008:
----------------------------------------
This must be a (relatively) recent enhancement to SQL; my decades old memory of the overall
structure of an SQL query was that ORDER BY was the "outermost" or final step in query processing.
I have these vague memories of various relational "purists" even arguing that the theoretical
reason
for this was that ORDER BY was not part of the relational theory proper, but rather was just
something
that user interfaces would do for display purposes.
That is, all processing was supposed to be done with mathematically pure "sets" of data, and
so
it was supposed to make no sense to ORDER BY an intermediate result.
I guess what I'm trying to figure out is why you would ever want to ORDER BY a portion of
your query
results, rather than ordering the final result.
Are there queries that will now return different results, as mathematical sets? Or is this
just a way
to sort some, but not all, of your data?
> Allow ORDER BY and FETCH/OFFSET in set operands
> -----------------------------------------------
>
> Key: DERBY-6008
> URL: https://issues.apache.org/jira/browse/DERBY-6008
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Reporter: Dag H. Wanvik
> Assignee: Dag H. Wanvik
> Attachments: derby-6008-a.diff, derby-6008-a.stat, derby-6008-b.diff, derby-6008-b.stat,
derby-6008-c.diff, derby-6008-c.stat
>
>
> Currently, Derby doesn't allow ORDER BY nested in a set operand, e.g. in the following
construct:
> (select i from t1 order by j offset 1 row) union
> (select i from t2 order by j desc offset 2 rows)
> This is allowed by the standard, as far as I can understand, cf. this quote from section
7.12 in SQL 2011:
> <query expression body> ::=
> <query term>
> | <query expression body> UNION [ ALL | DISTINCT ]
> [ <corresponding spec> ] <query term>
> | <query expression body> EXCEPT [ ALL | DISTINCT ]
> [ <corresponding spec> ] <query term>
> <query term> ::=
> <query primary>
> | <query term> INTERSECT [ ALL | DISTINCT ]
> [ <corresponding spec> ] <query primary>
> <query primary> ::=
> <simple table>
> | <left paren> <query expression body>
> [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause>
] <right paren>
> I.e. the left paren chooses the second alternative in the production for <query primary>.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira