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: r772327 - in /db/derby/code/branches/10.5/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:51:57 -0000
To: derby-commits@db.apache.org
From: dag@apache.org
Message-Id: <20090506155157.89C06238896C@eris.apache.org>

Author: dag
Date: Wed May  6 15:51:57 2009
New Revision: 772327

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

Back-ported derby-4212-2 to 10.5 branch, which fixes this issue and
adds a new test case for the scenario.


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

Modified: db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/execute/RowCountResultSet.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/execute/RowCountResultSet.java?rev=772327&r1=772326&r2=772327&view=diff
==============================================================================
--- db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/execute/RowCountResultSet.java (original)
+++ db/derby/code/branches/10.5/java/engine/org/apache/derby/impl/sql/execute/RowCountResultSet.java Wed May  6 15:51:57 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/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OffsetFetchNextTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OffsetFetchNextTest.java?rev=772327&r1=772326&r2=772327&view=diff
==============================================================================
--- db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OffsetFetchNextTest.java (original)
+++ db/derby/code/branches/10.5/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OffsetFetchNextTest.java Wed May  6 15:51:57 2009
@@ -649,6 +649,19 @@
         stm.close();
     }
 
+    /**
+     * Test that the values of offset and fetch first are not forgotten if
+     * a {@code PreparedStatement} is executed multiple times (DERBY-4212).
+     */
+    public void testRepeatedExecution() throws SQLException {
+        PreparedStatement ps = prepareStatement(
+                "select * from t1 order by b " +
+                "offset 2 rows fetch next 2 rows only");
+        String[][] expected = {{"1", "3"}, {"1", "4"}};
+        for (int i = 0; i < 10; i++) {
+            JDBC.assertFullResultSet(ps.executeQuery(), expected);
+        }
+    }
 
     private void queryAndCheck(
         Statement stm,