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] Updated: (DERBY-4079) Add support for SQL:2008 <result offset clause> and <fetch first clause> to limit result set cardinality
Date Thu, 05 Mar 2009 05:27:56 GMT

     [ https://issues.apache.org/jira/browse/DERBY-4079?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel

Dag H. Wanvik updated DERBY-4079:

    Attachment: derby-4079-2.stat

Thanks for looking at this patch, Rick!

Uploading a new version (#2) which contains a new JUnit test
(OffsetFetchNextTest) and that also addresses your comments, except
for the one one ResultSetFactory (more below). I moved the checking of
row count literal type to the parser as you suggested, but kept the
range checking in the bind phase (cleaner to me), and also now use the
more specific value node type.

I added examples as you suggested in the comment in DMLStatementNode.

I wasn't able to use a superclass for the SanityManager tests in the
GenericResultSetFactory (there is no superclass now, and even it there
were, the classname wold not be helpful (the factory not the result
set), the code you suggested would need to go into superclasses of the
result sets themselves, which means I would need to spread this code
around in the result set (super) classes. I just added that code to be
able to easily trace what result sets arise at execution time, let me
know if you think I should remove it (it could be part of another
patch really..).

Rerunning regressions.

> 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, 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