[
https://issues.apache.org/jira/browse/DERBY-6008?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13528002#comment-13528002
]
Dag H. Wanvik commented on DERBY-6008:
--------------------------------------
Adding a new patch, which handles the presence of two orderByLists for
SelectNode and UnionNode (qua VALUES top node) under an INTERSECT/EXCEPT: The
user specifed one arising from this issue, and the one used by the
implementation of the set operations. Currently we make no attempt to detect if
the same sort is specified by the operand's ORDER BY as the sort of all columns
used by the intersect/except comparison operation. The tree is stacked thus
when we are ready to generate code:
OrderByNode (op intersect/except)
|
RowCountNode (offset/fetch first)
|
OrderBynode (of operand)
The optimizer is informed by the lower order by if present, if not, by the
intersect/except sort. Added new test cases.
Rerunning regressions.
> 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, derby-6008-d.diff,
> derby-6008-d.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
