db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-4079) Add support for SQL:2008 <result offset clause> and <fetch first clause> to limit result set cardinality
Date Thu, 05 Mar 2009 18:51:57 GMT

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

Dag H. Wanvik commented on DERBY-4079:

Rick, as for the  reserved keywords I have been trying to get rid of those;
moving ROW back to unreserved was painless. Moving OFFSET to unreserved was harder, however,
since in its location there can be a choice conflict for the parser (with table correlation
I am trying to make a lookahead to circumvent this problem, which if, successful, would allow
OFFSET to be an identifier in all cases except ones like this:
    SELECT * from t OFFSET

This would work though
    SELECT * from t AS OFFSET

Here it is not clear if OFFSET is a correlation name of the start of a result offset clause.
So, even with this special handling, there could possibly be apps that would break. It might
be easier to just 
let OFFSET be a reserved keyword henceforth, since this would be cleaner (grammar, docs)?

> Add support for SQL:2008 <result offset clause> and <fetch first clause>
to limit result set cardinality
> --------------------------------------------------------------------------------------------------------
>                 Key: DERBY-4079
>                 URL: https://issues.apache.org/jira/browse/DERBY-4079
>             Project: Derby
>          Issue Type: New Feature
>          Components: SQL
>            Reporter: Dag H. Wanvik
>            Assignee: Dag H. Wanvik
>             Fix For:
>         Attachments: derby-4079-1.diff, derby-4079-1.stat, derby-4079-2.diff, derby-4079-2.stat,
derby-4079-docs-1.diff, derby-4079-docs-1.stat, derby-4079-docs-1.zip, derby-4079-docs-2.diff,
derby-4079-docs-2.stat, derby-4079-docs-2.zip, ref.zip
> SQL 2008 has added new syntax to support a direct way to limit the
> returned set of rows in a result set.  This allows an application to
> retrieve only some rows of an otherwise larger result set, similar to
> the popular LIMIT clauses use in some databases.
> Up till now, in Derby (and SQL) we have had to use the ROW_NUMBER()
> function in a nested subquery to achieve the effect of the <fetch
> first clause>, cf. DERBY-2998, a method which is rather more indirect
> and still not efficient (DERBY-3505), and primarily intended for OLAP
> functionality, perhaps.
> There has been no direct way to achieve the effect of the <result
> offset clause> via SQL.
> Syntax (cf. SQL 2008, section 7.13):
>        <result offset clause> ::= OFFSET <n> {ROW | ROWS}
>        <fetch first clause> ::=      FETCH {FIRST | NEXT} [<n>] {ROW | ROWS}
> where <n> is an integer. The two clauses syntactically follow the ORDER BY
> clause in the grammar.
> Note that both ORDER BY and the new clauses above are allowed also in
> subqueries in the new version of the SQL standard (section 7.13). I
> only propose to include this at the top level in DERBY for now. (ORDER
> BY is presently also not allowed in subqueries in Derby since SQL
> didn't allow for this until SQL 2008 either).

This message is automatically generated by JIRA.
You can reply to this email to add a comment to the issue online.

View raw message