SQLException when using selectCursor on DB2

[ddoaj97]

We are using the selectCursor method of SqlSession to stream a large result set from a DB2 database. On DB2 this operation always fails when trying to read the last object from the cursor's iterator specifically for select statements that use a result mapping with nested results.

After some investigation I have found that this error occurs because the DB2 driver behaves differently from most other databases. As soon as the ResultSet#next method returns false the DB2 driver starts throwing SQLExceptions on almost all methods (in this case on subsequent calls to ResultSet#getType and ResultSet#next). Most database drivers allow you to call these methods even after reaching the end of the result set but the behavior of DB2 is allowed by the JDBC spec because the Javadoc leaves the implementation open to the driver:

When a call to the next method returns false, the cursor is positioned after the last row. Any invocation of a ResultSet method which requires a current row will result in a SQLException being thrown.
If the result set type is TYPE_FORWARD_ONLY, it is vendor specified whether their JDBC driver implementation will return false or throw an SQLException on a subsequent call to next.

I had a look at the code and the exception occurs because the DefaultCursor calls the DefaultResultSetHandler#handleRowValues one last time after the end of the ResultSet was reached to check if any more objects remain. On DB2 the ResultSet is already closed automatically at this point causing the exception. A quick workaround that I will supply in a pull request would be to add a ResultSet#isClosed check before interacting with the ResultSet.

MyBatis version

3.4.6

Database vendor and version

IBM DB2 LUW version 9.7 (JDBC driver: db2jcc4_9.7fp11.jar)

Test case or example project

I cannot provide a working example project because the problem only occurs when run with a DB2 database. However, the existing integration tests in org.apache.ibatis.submitted.cursor_nested.CursorNestedTest should already run into the same problem when using a DB2 database.
To demonstrate the issue I will add a pull request with a unit test that triggers the exception using a mocked ResultSet instance. I have also implemented a quick fix that works around the problem.

Steps to reproduce

Execute a select query with "resultOrdered" set to "true" using a Mybatis cursor (either by using a Mapper interface returning a Cursor instance or by calling the selectCursor method of SqlSession) and where the result map contains one or more nested result maps. When run using the DB2 database driver this results in an exception when reading the last object from the iterator.

Expected result

We should be able to read all objects from the Mybatis Cursor without receiving any exceptions.

Actual result

When we try to consume the last object from the Cursor's iterator we receive the following exception:

java.lang.RuntimeException: com.ibm.db2.jcc.am.SqlException: [jcc][t4][10120][10898][4.14.146] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null
	at org.apache.ibatis.cursor.defaults.DefaultCursor.fetchNextObjectFromDatabase(DefaultCursor.java:143)
	at org.apache.ibatis.cursor.defaults.DefaultCursor.fetchNextUsingRowBound(DefaultCursor.java:127)
	at org.apache.ibatis.cursor.defaults.DefaultCursor$CursorIterator.hasNext(DefaultCursor.java:194)
	at com.google.common.collect.Iterators.addAll(Iterators.java:366)
	at com.google.common.collect.Lists.newArrayList(Lists.java:165)
	at com.google.common.collect.Lists.newArrayList(Lists.java:150)
	at eu.vandevelde.m3.customer.CustomerRepositoryITTest.findCustomersModifiedBetween(CustomerRepositoryITTest.java:76)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
	at org.springframework.test.context.junit4.statements.RunBeforeTestExecutionCallbacks.evaluate(RunBeforeTestExecutionCallbacks.java:73)
	at org.springframework.test.context.junit4.statements.RunAfterTestExecutionCallbacks.evaluate(RunAfterTestExecutionCallbacks.java:83)
	at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
	at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
	at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
	at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:251)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:97)
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
	at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
	at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:190)
	at org.apache.maven.surefire.junit4.JUnit4Provider.execute(JUnit4Provider.java:365)
	at org.apache.maven.surefire.junit4.JUnit4Provider.executeWithRerun(JUnit4Provider.java:273)
	at org.apache.maven.surefire.junit4.JUnit4Provider.executeTestSet(JUnit4Provider.java:238)
	at org.apache.maven.surefire.junit4.JUnit4Provider.invoke(JUnit4Provider.java:159)
	at org.apache.maven.surefire.booter.ForkedBooter.invokeProviderInSameClassLoader(ForkedBooter.java:383)
	at org.apache.maven.surefire.booter.ForkedBooter.runSuitesInProcess(ForkedBooter.java:344)
	at org.apache.maven.surefire.booter.ForkedBooter.execute(ForkedBooter.java:125)
	at org.apache.maven.surefire.booter.ForkedBooter.main(ForkedBooter.java:417)
Caused by: com.ibm.db2.jcc.am.SqlException: [jcc][t4][10120][10898][4.14.146] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null
	at com.ibm.db2.jcc.am.ed.a(ed.java:661)
	at com.ibm.db2.jcc.am.ed.a(ed.java:60)
	at com.ibm.db2.jcc.am.ed.a(ed.java:103)
	at com.ibm.db2.jcc.am.ResultSet.checkForClosedResultSet(ResultSet.java:4599)
	at com.ibm.db2.jcc.am.ResultSet.getType(ResultSet.java:2542)
	at com.zaxxer.hikari.pool.HikariProxyResultSet.getType(HikariProxyResultSet.java)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.skipRows(DefaultResultSetHandler.java:379)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValuesForNestedResultMap(DefaultResultSetHandler.java:867)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValues(DefaultResultSetHandler.java:328)
	at org.apache.ibatis.cursor.defaults.DefaultCursor.fetchNextObjectFromDatabase(DefaultCursor.java:141)
	... 40 more

[ddoaj97]

See pull request #1346 for a unit test and a potential fix.

[harawata]

Hi @ddoaj97 ,

Thank you for the detailed report. Your analysis looks perfect.
Regarding the fix, though, there is another related issue I would like to fix along with this, so let me look into it.

I should also mention that there is a workaround [1].

[1] There is a driver property allowNextOnExhaustedResultSet to change the behavior (e.g. jdbc:db2://192.168.62.100:50000/test:allowNextOnExhaustedResultSet=1;. Here is the technote.

[harawata]

Hi @ddoaj97 ,
I have committed the fix.
It would be appreciated if you could try the latest 3.5.0-SNAPSHOT and see if there is any other issue.

[ddoaj97]

Hi @harawata ,

I'm sorry about my late reply but I found some time today to check the latest 3.5.0-SNAPSHOT in my application. The problem seems to be fixed because I no longer see the exception occurring.

Thanks a lot for your fix!