Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Subject: svn commit: r772299 - in /db/derby/code/trunk/java:
 engine/org/apache/derby/impl/sql/execute/RowCountResultSet.java
 testing/org/apache/derbyTesting/functionTests/tests/lang/OffsetFetchNextTest.java
Date: Wed, 06 May 2009 15:18:55 -0000
To: derby-commits@db.apache.org
From: dag@apache.org
Message-Id: <20090506151855.E939D23888CA@eris.apache.org>

Author: dag
Date: Wed May  6 15:18:54 2009
New Revision: 772299

URL: http://svn.apache.org/viewvc?rev=772299&view=rev
Log:
DERBY-4212 Prepared statement with OFFSET/FETCH gives different results on subsequent execute

Patch DERBY-4212-2, which fixes this issue by resetting state
variables in the close method of RowCountResultSet, and adds a new
test case for this use case to OffsetFetchNextTest.


Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RowCountResultSet.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OffsetFetchNextTest.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RowCountResultSet.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RowCountResultSet.java?rev=772299&r1=772298&r2=772299&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RowCountResultSet.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RowCountResultSet.java Wed May  6 15:18:54 2009
@@ -49,8 +49,20 @@
     // life of object.
     final NoPutResultSet source;
     final private boolean runTimeStatsOn;
-    private long offset;
-    private long fetchFirst;
+    final private long offset;
+    final private long fetchFirst;
+
+    /**
+     * True if we haven't yet fetched any rows from this result set.
+     * Will be reset on close so the result set is ready to reuse.
+     */
+    private boolean virginal;
+
+    /**
+     * Holds the number of rows returned so far in this round of using the
+     * result set.  Will be reset on close so the result set is ready to reuse.
+     */
+    private long rowsFetched;
 
     /**
      * RowCountResultSet constructor
@@ -87,6 +99,8 @@
 
         this.offset = offset;
         this.fetchFirst = fetchFirst;
+        virginal = true;
+        rowsFetched = 0;
 
         /* Remember whether or not RunTimeStatistics is on */
         runTimeStatsOn =
@@ -159,24 +173,26 @@
 
         beginTime = getCurrentTimeMillis();
 
-        if (offset > 0) {
+        if (virginal && offset > 0) {
+            // Only skip rows the first time around
+            virginal = false;
+
+            long offsetCtr = offset;
+
             do {
                 result = source.getNextRowCore();
-                offset--;
+                offsetCtr--;
 
-                if (result != null && offset >= 0) {
+                if (result != null && offsetCtr >= 0) {
                     rowsFiltered++;
                 } else {
                     break;
                 }
 
             } while (true);
-
-            // only skip row first time
-            offset = 0;
         } else {
 
-            if (fetchFirst != -1 && rowsSeen >= fetchFirst) {
+            if (fetchFirst != -1 && rowsFetched >= fetchFirst) {
                 result = null;
             } else {
                 result = source.getNextRowCore();
@@ -185,6 +201,7 @@
 
 
         if (result != null) {
+            rowsFetched++;
             rowsSeen++;
         }
 
@@ -248,6 +265,10 @@
             }
         }
 
+        // Reset state for result set reuse, if any
+        virginal = true;
+        rowsFetched = 0;
+
         closeTime += getElapsedMillis(beginTime);
     }
 

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OffsetFetchNextTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OffsetFetchNextTest.java?rev=772299&r1=772298&r2=772299&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OffsetFetchNextTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OffsetFetchNextTest.java Wed May  6 15:18:54 2009
@@ -649,10 +649,8 @@
     /**
      * Test that the values of offset and fetch first are not forgotten if
      * a {@code PreparedStatement} is executed multiple times (DERBY-4212).
-     * <b>NOTE:</b> Disabled until the bug is fixed. Remove x from the method
-     * name to enable it.
      */
-    public void xtestRepeatedExecution() throws SQLException {
+    public void testRepeatedExecution() throws SQLException {
         PreparedStatement ps = prepareStatement(
                 "select * from t1 order by b " +
                 "offset 2 rows fetch next 2 rows only");