db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (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 Wed, 04 Mar 2009 22:33:56 GMT

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

Rick Hillegas commented on DERBY-4079:

Thanks for the experimental patch, Dag. The overall approach looks good to me. I have a couple
small comments:


The new methods have generic (possibly cloned?) javadoc.


It seems to me that since the new offset and fetchFirst fields are known to be NumericConstantNodes,
then they should be declared as the most refined type possible, rather than as generic ValueNodes.
 I think this will make the code easier to understand.


Thanks for the explanation of why you are wrapping the inner result set at that particular
point in the code. I find that these explanations are easier to understand if they include
a sample problem query which would break if the code were placed somewhere else.


Do the new FETCH and OFFSET keywords need to be reserved? I realize that the standard may
suggest that they should be, but there are plenty of examples of Derby downgrading reserved
keywords to non-reserved keywords in order to avoid breaking legacy applications. Same question
for ROW, which appears to have been moved from non-reserved to reserved status.

Similar comment to that for CursorNode: if the new clauses are just NumericConstantNodes,
then it makes sense to me to declare them as that refined type.

I wonder if the offsetClause() and fetchFirstClause() productions could use numericLiteral()
rather than literal(). With a little more work, you might be able to get the parser to guarantee
that the literal is a non-negative integer and you might be able to skip this check at bind()


I wonder if all of the duplicated debug code could be pulled up into a superclass constructor.
Something like 

		if (SanityManager.DEBUG && SanityManager.DEBUG_ON("DumpResultSetGeneration")) {
			SanityManager.GET_DEBUG_STREAM().print("Generated " + getClass().getName() + "\n");

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