Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <1559206890.1236131456235.JavaMail.jira@brutus>
Date: Tue, 3 Mar 2009 17:50:56 -0800 (PST)
From: "Dag H. Wanvik (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Updated: (DERBY-4079) Add support for SQL:2008 <result
 offset clause> and <fetch first clause> to limit result set cardinality
In-Reply-To: <336682448.1236050096298.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


     [ 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-1.stat
                derby-4079-1.diff

Uploading a patch for this functionality (only for comments at this
point).  It does not yet contain a new JUnit test for the feature,
I'll add that in the next spin.

I have tested the patch ad hoc, and it seems for work for what I have
thrown at it so far.

Since this feature cuts across a lot of underlying result sets and
modes (read-only, scrollable, updatable, subqueries etc), it is hard
to write an exhaustive test. To test whether the presence of an
additional result set on top of other result sets breaks anything, I
have done the following experiment: I modified the parser so as to
always stick in an OFFSET 0 ROWS clause for a top level SELECT. This
should have no impact on the result set returned.  With that
modification, I ran the regressions. I uncovered some small issues
with updatable result sets in this way.  Presently, the only tests
which fail are canon based tests which compare the execution plan with
a canon (a diff is to be expected here). For a couple of those I
compared the plans manually to verify that the only difference was the
presence of a wrapper result set.

Patch details:

M      java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj

Add the new syntax and stick offset/fetch first values into CursorNode.

M      java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java
M      java/engine/org/apache/derby/impl/sql/compile/C_NodeNames.java
M      java/engine/org/apache/derby/iapi/sql/compile/C_NodeTypes.java

Accommodate the new node type RowCountNode.

A      java/engine/org/apache/derby/impl/sql/compile/RowCountNode.java

This node is inserted at optimize time, see CursorNode,
DMLStatementNode changes. At generation time it will insert a
RowCountResultSet over the top SELECTs result set, but underneath any
ScrollInsensitiveResultSet.

M      java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java

Adds copying of table name and schema name to the cloneMe method. his
was needed to handle updatable result sets which expect to see the
table name in the top result columns. Is this change safe?

M      java/engine/org/apache/derby/impl/sql/compile/CursorNode.java
M      java/engine/org/apache/derby/impl/sql/compile/DMLStatementNode.java

Bind offset/fetch first values (type check, range check) and add a
RowCountNode to the abstract syntax tree at optimize time. I found
that inserting this node prior to optimizing the underlying query did
not work, notably GROUP BY failed.

M      java/engine/org/apache/derby/impl/sql/compile/ValueNode.java

Added a method to check if a value is an integer or a bigint, needed
at bind time of the row counts.

A      java/engine/org/apache/derby/impl/sql/execute/RowCountResultSet.java

This new result set implements the filtering required by this
functionality. It mostly forwards to it child result set otherwise.

A      java/engine/org/apache/derby/impl/sql/execute/rts/RealRowCountStatistics.java
M      java/engine/org/apache/derby/impl/sql/execute/RealResultSetStatisticsFactory.java

Adds run time statistics handling for the new result set.

M      java/engine/org/apache/derby/impl/sql/execute/NoPutResultSetImpl.java

Relaxed a "final" to be able to override clearCurrentRow in
RowCountResultSet. RowCountResultSet#clearCurrentRow actually calls
clearCurrentRow of its source result set, since
RowCountResultSet#getCurrentRow also asks its source result set for
the current row. Forwarding these to the child, may not be entirely
kosher, but I had to do it this way to make it work with the updatable
cursors. Suggestions of better ways to handle this are welcome.

M      java/engine/org/apache/derby/impl/sql/execute/ScrollInsensitiveResultSet.java

updateRow will look at the result set for a top ProjectRestrictNode
and use it for projection. I added code here to get at the
ProjectRestrictNode from underneath a RowCountResultSet.

M      java/engine/org/apache/derby/impl/sql/execute/GenericResultSetFactory.java
M      java/engine/org/apache/derby/iapi/sql/execute/ResultSetFactory.java

Accommodate the new result set type.

M      java/engine/org/apache/derby/iapi/types/TypeId.java

Made BIGINT_ID public, needed by the new method in ValueNode (checkIsInteger).

M      java/engine/org/apache/derby/loc/messages.xml
M      java/shared/org/apache/derby/shared/common/reference/SQLState.java

New error messages/execution plan i18n strings and English text for
them.

M      tools/jar/DBMSnodes.properties


> 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
>         Attachments: derby-4079-1.diff, derby-4079-1.stat, 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} ONLY
> 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.