ResultSet cursor query methods does not behave as specified by the Java JDBC API

Background:
The normal pattern to fetch data with the Java JDBC API is this:

        ResultSet resultSet = statement.executeQuery("SELECT * FROM my_table");

        while (resultSet.next()) {

            System.out.println(resultSet.getString(2));

        }

When this pattern is used everything works as expected. However if for some reason the SQL code is to be adapted to the java.util.Iterator pattern thing gets a bit complicated. The iterator pattern looks like this:

        Iterator<String> iterator = list.iterator();

        while (iterator.hasNext()) {

            System.out.println(iterator.next());

        }

It would be possible to create an abstraction class that exposes the ResultSet as an Iterator (and this has happened in the code I’m working out now). In the code I’m working on the iterator.next() is implemented directly as resultSet.next(). But the iterator.hasNext() is implemented by some weird combination of the “is”-methods in ResultSet: isBeforeFirst(), isFirst(), isLast(),isAfterLast() and isClosed().

These “is”-methods doesn’t work as expected in MariaDB, hence this ticket.

How to reproduce:

1. Download the attached JUnit test class ResultSetCursorTest.java.
2. Run test cases using the MariaDB JDBC connector.
3. Run test cases uing Mysql JDBC connector: modify the file in getConnection() to use Mysql JDBC instead of MariaDB. For this to work you need to add Mysql JDBC as a dependency in the pom.xml and also make an adaption of the MariaDB JDBC code so it does not consumes the “jdbc:mysql” URL. See the comment in my code for details.
4. Run the test cases using H2 JDBC connector: modify the file in getConnection() to use H2 JDBC. For this to work you need to add H2 as a dependency in pom.xml. H2 is used as an in-memory database and does not need a standalone server.

I have both used a 1.1.7-ish version of and also version 534 (http://bazaar.launchpad.net/~maria-captains/mariadb-java-client/trunk/revision/534) to test this.

Actual:

• With MariaDB JDBC: all test cases fail, except debugIterateTest() that will pass. (debugIterateTest is not really a test case, it is more a debug-help-test case that prints some debug information.) I have written a comment in each test case showing where MariaDB JDBC will fail.
• With Mysql JDBC: all test cases will pass.
• With H2 JDBC: all test cases will pass.

Expected:
All test should probably pass with all connectors. But I’m a bit uncertain about some.

The documentation of ResultSet can be found here: http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html. I will try to break down each method as best I can.

isClosed():
From API: “Retrieves whether this ResultSet object has been closed. A ResultSet is closed if the method close has been called on it, or if it is automatically closed”

From what I can see in the code the MariaDB JDBC closes the ResultSet very soon after the server has responded. I guess it could be debatable what in the API documentation qualifies as “automatically closed”. It is clear however that MariaDB JDBC behaves differently than Mysql and H2.

isBeforeFirst():
In general I think that MariaDB behaves correct for this method. However, from the Java API: “SQLException - if a database access error occurs or this method is called on a closed result set”.

Since my test code called the close() method I guess to make it conformant to the API the isBeforeFirst() method should throw an SQLException.

isFirst():
From the API: “true if the cursor is on the first row; false otherwise”

This is one case where MariaDB behaves differently if there are zero rows returned. For a zero row SELECT this method should probably never return true? Since the cursor never is on a row?

In the multiple row SELECT I think MariaDB JDBC behaves more or less correct, with the exception of not throwing an SQLException after the ResultSet is closed.

isLast():
From the API: “true if the cursor is on the last row; false otherwise”

With a zero row SELECT the cursor could never be on the last row, because there is no row? This is basically the same argument as the problem for isFirst().

In the multiple row SELECT I think MariaDB JDBC behaves more or less correct, with the exception of not throwing an SQLException after the ResultSet is closed.

isAfterLast():
From the API: “true if the cursor is after the last row; false if the cursor is at any other position or the result set contains no rows”

With a zero row SELECT this method should always return false, since it is explicitly stated in the API.

In the multiple row SELECT I think MariaDB JDBC behaves more or less correct, with the exception of not throwing an SQLException after the ResultSet is closed.

Backwards compatibility problems:
I think that some of my test cases shows direct bugs. And some of my test cases are more debatable if they are bugs depending on how the API is interpreted. If the bugs are fixed I’m more or less certain that you will end up with problems with application software that is expecting the MariaDB JDBC to behave in a certain way.

From my perspective (the switching from Mysql JDBC to MariaDB JDBC-perspective) I think they should behave the same.