[ https://issues.apache.org/jira/browse/DERBY-6008?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13512063#comment-13512063 ] 

Dag H. Wanvik edited comment on DERBY-6008 at 12/6/12 8:13 PM:
---------------------------------------------------------------

Thanks, Knut. I agree. Uploaded a version "c" of the patch which opens up for VALUES also. As far as I can see it's valid SQL. I kept a check on the node types in nonJoinQueryPrimary, albeit simplified in structure as you suggested, to guard against future extensions, although that's probably unnecessary now (checking for select, union or rowresultset nodes; the two latter used for VALUES).

Added suitable bind-time operations for SetOperatorNode and RowResultSetNode. Note the boolean flag "dontRemoveOrderingColumns" which guards against removal when the node isn't really a UNION but represents the top level for a VALUES clause with an ORDER BY.

Added more tests for the new cases. Rerunning regressions.


                
      was (Author: dagw):
    Thanks, Knut. I agree. Uploaded a version "c" of the patch which opens up for VALUES also. As far as I can see it's valid SQL. I kept a check on the node types in nonJoinQueryPrimary, albeit simplified in structure as you suggested, to guard against future extensions, although that's probably unnecessary now (checking for select, union or rowsetset nodes; the two latter used for VALUES).

Added suitable bind-time operations for SetOperatorNode and RowResultSetNode. Note the boolean flag "dontRemoveOrderingColumns" which guards against removal when the node isn't really a UNION but represents the top level for a VALUES clause with an ORDER BY.

Added more tests for the new 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
>
>
> 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