Problem when iterating jdbc resultSet

General
Enthusiast

Problem when iterating jdbc resultSet

Hello!

We discovered some strange behaviour of a cursor in a result set, when extracting data from the Teradata DB using jdbc. The versions that we use: Teradata database 14.10.00.02, and we've tested it with jdbc driver versions 14.00.00.37 and 15.00.00.20.

You can find the source code of our test program below.

public class FetchTester {
private static final String DRIVER = "com.teradata.jdbc.TeraDriver";
private static final String URL = "jdbc:teradata://<db_ip>/<db_schema>";
private static final String USER = "user";
private static final String PASS = "password";

private static final String CREATE = "CREATE TABLE fetchtest ("//
+ "a INTEGER NOT NULL,"//
+ "b INTEGER NOT NULL,"//
+ "c BIGINT NOT NULL,"//
+ "PRIMARY KEY (c))";//
private static final String WRITE = "INSERT INTO fetchtest VALUES (1,1,?)";
private static final String READ = "SELECT TOP 100 PERCENT c FROM fetchtest "//
+ "WHERE a = b "//
+ "ORDER by c";
private static final String DROP = "DROP TABLE fetchtest";

private static final int ROWS = 10000;
private static final int FETCH = 10;
private static final int CHUNK = 100;

public static void test(Connection conn) throws Exception {
PreparedStatement stmt = null;
try {
stmt = conn.prepareStatement(READ,
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(FETCH);
System.out.println("extracting the data...");
ResultSet result = stmt.executeQuery();
result.setFetchSize(FETCH);
for (int i = 1; i <= CHUNK; i++) {
result.absolute(i);
long j = result.getLong(1);
System.out.println(" row number = " + i + " value = " + j);
}
} finally {
if (stmt != null)
stmt.close();
}
}

public static Connection setUp() throws Exception {
Connection conn = null;
Statement stmt1 = null;
PreparedStatement stmt2 = null;
try {
Class.forName(DRIVER);
System.out.println("connecting to database...");
conn = DriverManager.getConnection(URL, USER, PASS);
conn.setAutoCommit(false);
System.out.println("creating a table...");
stmt1 = conn.createStatement();
stmt1.execute(CREATE);
stmt1.close();
stmt1 = null;
conn.commit();
System.out.println("filling the table...");
stmt2 = conn.prepareStatement(WRITE);
for (int i = 0; i < ROWS; i++) {
stmt2.setInt(1, i);
stmt2.addBatch();
}
stmt2.executeBatch();
stmt2.close();
stmt2 = null;
conn.commit();
return conn;
} catch (Exception e) {
if (conn != null)
conn.close();
throw e;
} finally {
if (stmt1 != null)
stmt1.close();
if (stmt2 != null)
stmt2.close();
}
}

public static void tearDown(Connection conn) throws Exception {
if (conn == null)
return;
Statement stmt = null;
try {
System.out.println("dropping the table...");
stmt = conn.createStatement();
stmt.execute(DROP);
stmt.close();
stmt = null;
conn.commit();
} finally {
if (stmt != null)
stmt.close();
conn.close();
}
}

public static void main(String[] args) throws Exception {
Connection conn = setUp();
try {
test(conn);
} finally {
tearDown(conn);
}
}
}

When the program iterates the resultset, we expect that the result of iteration does not depend on the fetch size. However, when the SELECT statement 

SELECT TOP 100 PERCENT c FROM fetchtest WHERE a = b  ORDER by c

is used to collect the resultSet, result.absolute(i) goes exactly 0.5*FETCH back every time in the step when the new portion of data is read from the database (step with numbers FETCH*n + 1). With FETCH = 10, the output will look as follows:

  row number = 1 value = 0
row number = 2 value = 1
row number = 3 value = 2
row number = 4 value = 3
row number = 5 value = 4
row number = 6 value = 5
row number = 7 value = 6
row number = 8 value = 7
row number = 9 value = 8
row number = 10 value = 9
row number = 11 value = 5
row number = 12 value = 6
row number = 13 value = 7
row number = 14 value = 8
row number = 15 value = 9
row number = 16 value = 10
row number = 17 value = 11
row number = 18 value = 12
row number = 19 value = 13
row number = 20 value = 14
row number = 21 value = 10
row number = 22 value = 11...

Interestingly, when we use the same SELECT statement without WHEN-clause, or without "TOP X PERCENT", the iteration works fine.

What could be a reason for this?

Many thanks!

2 REPLIES
Teradata Employee

Re: Problem when iterating jdbc resultSet

Thank you for reporting this problem! This appears to be a Teradata Database problem, and DBS DR 173418 has been created to cover this issue.

Enthusiast

Re: Problem when iterating jdbc resultSet

Thank you for the fast reply!