[DERBY-5425] Updateable holdable ResultSet terminates early after 65638 updates

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.7.1.1
• Fix Version/s: 10.8.3.0, 10.9.2.2, 10.10.1.1
• Component/s: JDBC
• Labels: derby_triage10_9

Environment

------------------ Java Information ------------------
Java Version: 1.6.0_26
Java Vendor: Sun Microsystems Inc.
Java home: D:\Program Files (x86)\Java\jre6
Java classpath: .;..\derby.jar
OS name: Windows 7
OS architecture: x86
OS version: 6.1
Java user name: Andrew
Java user home: D:\Users\Andrew
Java user dir: E:\workspace\DerbyBug\bin
java.specification.name: Java Platform API Specification
java.specification.version: 1.6
java.runtime.version: 1.6.0_26-b03
--------- Derby Information --------
JRE - JDBC: Java SE 6 - JDBC 4.0
[E:\workspace\DerbyBug\derby.jar] 10.7.1.1 - (1040133)
------------------------------------------------------
----------------- Locale Information -----------------
------------------------------------------------------

• Urgency: Normal
• Issue & fix info: Repro attached
• Bug behavior facts: Seen in production, Wrong query result
• Assignee: Knut Anders Hatlen
• Reporter: Andrew Johnson
• Created: 20/Sep/11 21:50
• Updated: 25/Nov/12 20:14
• Resolved: 18/Sep/12 12:34

Description

After at least 65638 updates to an indexed column have been done via an updateable holdable resultset and the transaction is committed ResultSet.next() returns false even if more rows exist to be returned.

The following program should output "Total: 100000" but instead outputs "Total: 65638".

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DerbyBug {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
Connection conn = DriverManager.getConnection("jdbc:derby:TestDB;create=true");

conn.setAutoCommit(false);

Statement createStmt = conn.createStatement();
createStmt.executeUpdate("CREATE TABLE test (a INT)");
createStmt.executeUpdate("CREATE INDEX idxa ON test(a)");
createStmt.close();

PreparedStatement insertStmt = conn.prepareStatement("INSERT INTO test(a) VALUES ");

for (int i = 0; i < 100000; ++i)

{ insertStmt.setInt(1, i); insertStmt.executeUpdate(); }

insertStmt.close();

conn.commit();

Statement selectStmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE, ResultSet.HOLD_CURSORS_OVER_COMMIT);
ResultSet rs = selectStmt.executeQuery("SELECT a FROM test FOR UPDATE");

int count = 0;
while (rs.next())

{ rs.updateInt(1, count); rs.updateRow(); count++; conn.commit(); }

rs.close();
selectStmt.close();
conn.commit();
conn.close();

System.out.println("Total: " + count);

try

{ DriverManager.getConnection("jdbc:derby:;shutdown=true"); }

catch (SQLException e) {
if (!e.getSQLState().equals("XJ015"))

{ throw e; }

}
}
}

Issue Links

• is required by: DERBY-5923 10.8 Backport issue (Fall 2012)