db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6008) Allow ORDER BY and FETCH/OFFSET in set operands
Date Thu, 06 Dec 2012 16:04:09 GMT

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

Knut Anders Hatlen commented on DERBY-6008:
-------------------------------------------

I tested some queries in ij, and the patch seems to do the right thing.

The syntax check in the set operations seems to be a little stricter than in a top-level query,
as we accept

  VALUES 3, 2, 1 ORDER BY 1

but not

  (VALUES 3, 2, 1 ORDER BY 1) UNION VALUES 4

If the former is valid SQL, we should probably allow the latter too, but I haven't checked
what the standard says.

Apart from that, it looks fine to me.

And a tiny nit, feel free to ignore: In the error checking added to nonJoinQueryPrimary(),
the two inner if statement levels could probably be flattened to a single level, which might
make it a little clearer.
                
> 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
>
>
> 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

Mime
View raw message