All Forums General
k3na 19 posts Joined 09/14
15 Sep 2014
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!

 

tomnolan 594 posts Joined 01/08
15 Sep 2014

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.

k3na 19 posts Joined 09/14
16 Sep 2014

Thank you for the fast reply!

You must sign in to leave a comment.