Derby 10.3.1.4 and 10.3.2.1 break scrollable result sets? Hibernate Query.setFirstResult and/or setMaxResults

• Key: DERBY-3397
• Type: Bug
• Status: Resolved
• Resolution: Fixed
• Priority: Major
• Assignee: Dag H. Wanvik
• Reporter: Michael Lossos
• Component/s: JDBC
• Affects Version/s: 10.3.1.4, 10.3.2.1, 10.4.2.0, 10.5.1.1
• Fix Version/s: 10.4.2.0, 10.5.1.1

Description

I am attempting to upgrade our product from Derby 10.2.2.0 to 10.3.2.1. With all other things held constant, if I change the derby.jar from 10.2.2 to 10.3.2.1, our calls to set the (JDBC) first result and max results (max rows) no longer function properly, such that no results are returned beyond first result 200, max results 100 (max rows 300), even when the table has over 1000 rows. 2 of the 11 columns of this table are indexed

We use Hibernate's result pagination via Query.setFirstResult and setMaxResults which, in org.hibernate.loader.Loader.advance(), uses java.sql.ResultSet.advance when scrollable result sets are available, and as expected org.apache.derby.impl.jdbc.EmbedDatabaseMetaData reports that scrollable result sets are available for both Derby 10.2.2 and 10.3.2.1.

The following is pseudo code for what we're doing with Hibernate:

int pageSize = 100;
int count = ... // select count(*) from OURTABLE;
for( int firstResult = 0; firstResult < count; firstResult += pageSize) {
        Query query = session.createQuery( "from OurHibernateObject"); // select * from OURTABLE
        query.setFirstResult( firstResult );
        query.setMaxResults( pageSize );
        List objList = query.list();
        // results are fine for firstResult 100 and 200,
        // but beyond that no results are returned with a >1000 row table!
}

When settings max results, Hibernate correctly sets max rows as follows from org.hibernate.loader.Loader.setMaxRows:

st.setMaxRows( selection.getMaxRows().intValue() + getFirstRow( selection ) );

Which is calling into org.apache.derby.impl.jdbc.EmbedPreparedStatement40. This code path doesn't change between Derby 10.2.2 and 10.3.2.1.

I've tried completely recreating the database to remove any possible problems with soft / full upgrades, but this didn't fix the problem. I tried 10.3.1.4 but this also exhibits the bug.

This seems like a fairly basic regression (surely a Derby test would fail if scrollable results were broken). I'm wondering if there's another factor at work here? Please help me to describe whatever else is necessary for you to reproduce this. (I can't post our table schema or our code.) I apologize in advance if this our own mistake but as I said, I'm only updating the derby.jar.

Thanks for all the hard work on Derby!

Comments

[Dag H. Wanvik]

If you can produce a plain SQL repro (or even a simple repro
using Hibernate) it would help a lot!

I am not familiar with the Hibernate mapping, so it would be nice if you could trace
the relevant SQL being executed here (server traceing):

by setting derby.language.logStatementText=true
and derby.stream.error.logSeverityLevel=0

The trace output can be found in derby.log; it would be helpful if you could attach that trace to this issue.

a) Is the scrollable result set also updatable?

b) Do you use the Derby network client driver or do you run embedded Derby?
    If you use the client driver, you could trace there also, see
    http://db.apache.org/derby/docs/10.3/adminguide/cadminappsclienttracing.html

c) Do the rows in OURTABLE contain any LOB objects (LOBS, CLOBS)?

[martin kovacik]

I can confirm this bug.
I use Derby 10.3.2.1. and Hibernate 3.2.5ga. My page size was 30. I was only able to display 10 pages (so records 1-300). Everything beyond that resulted in empty list. Table which was queried contains more than 50000 records. I used EmbeddedDriver. After switching to ClientDriver the pagination works perfectly.

[Michael Lossos]

I was also using the EmbeddedDriver when I encountered this problem. Sorry that I haven't had a chance to put together a simple reproduction case.

[Dag H. Wanvik]

Martin, are you able to provide any answer to the questions I raised before?
Can you reliably reproduced this error? If so, would you be able to
do the tracing I suggested?

It would be very helpful in trying to reproduce this so we can fix it!
Thanks, Dag

[Dag H. Wanvik]

Interesting that it works correctly with the client driver, thanks.

[Michael Lossos]

Reproduction case for DERBY-3397. Includes source, libs, and Eclipse project.

[Michael Lossos]

I've created a reproduction case for this bug. To answer the earlier questions:

a) Is the scrollable result set also updatable?
-- I don't think so but I'm not sure.
b) Do you use the Derby network client driver or do you run embedded Derby?
-- Embedded.
c) Do the rows in OURTABLE contain any LOB objects (LOBS, CLOBS)?
-- No LOBs.

[Michael Lossos]

I noticed while creating the reproduction case that Derby's scrollable results work when using the Hibernate connection pool but fail with the c3p0 connection pool. This makes the bug look suspiciously like a bug in c3p0 and not in Derby. However, when I use c3p0 with hsqldb (in memory), I do not see the scrollable result set problem. Furthermore, c3p0 works fine with Derby 10.2.2.0 but fails with Derby 10.3.2.1. (You can see this by dropping in the different derby jars and running the repro case again.)

Dag, I appreciate you looking into this.

[Dag H. Wanvik]

Great, thanks for the repro, Michael! I downloaded Hibernate today and starting fiddling
with an example to try to approximate your use case, but the repro will save me from guesswork!

[Dag H. Wanvik]

This thread mentions a bug seen when running with c3p0:

http://www.nabble.com/ERROR-08000:-Connection-closed-by-unknown-interrupt-td16060173.html

It may possibly be related. Micael, can you have a look at derby.log and see if there any stack traces similar that in the thread?
Meanwhile I am working on getting the repro to run.

[Michael Lossos]

I'm not seeing any errors in my derby log when:

derby.language.logStatementText=true
derby.stream.error.logSeverityLevel=0

Though connections being closed and not being re-opened seems a likely culprit. As I said initially, you can hold all other things constant and only change Derby from 10.2.2.0 to 10.3.2.1 to get this error.

[Dag H. Wanvik]

I am now able to reproduce the error using the provided repro, using Derby head of trunk and Hibernate 3.2.6
I also see it working with 10.2.1.6. Thanks Michael!

[Dag H. Wanvik]

The patch the changes the behavior of Derby such that it starts to fail on this test (not saying that there is a Derby errror yet), is svn revision 540921 (DERBY-827: Performance can be improved by re-using language ResultSets
across Activation executions). Earlier it works and later it fails consistently.

[Dag H. Wanvik]

I won't be able to work on this issue for about two weeks. If someone else wants to pick it up, feel free

[Dag H. Wanvik]

This one-liner patch seems to fix the problem.

When reuse of sets was introduced in DERBY-827, for
ScrollInsensitiveResultSets, the openCore method has to
correctly reinitialize the result set object, but ScrollInsensitiveResultSet#maxRows
 must have fallen through the cracks. It does not get reset, so
it will effectively inherit the value first usage.

In the app, the first time a scroll insensitive result set is used, the max is 200
(the second "page" uses absolute positioning to pos 100).
Then, when the third page reuses the rs,
the activation has a maxRows value of 300, but the rs does not pick this up
and the error ensues, since next will not return more rows beyond 200.

I have not run regression tests.

[Michael Lossos]

Thanks for the patch! Is more work needed to incorporate this into the next Derby release?

[Kristian Waagan]

I'm running the existing regression tests, and I also want to try writing a new simple regression test for the problem.

[Kristian Waagan]

All tests ran without failures (revision 650042M).
derbyall:
269 Tests Run
100% Pass (269 tests passed)
 0% Fail (0 tests failed)
0 Suites skipped

suites.All: OK (9864 tests)

[Michael Lossos]

FYI I wrote the repro case in a way that should be fairly easy to adapt into a unit test (though I'm not familiar with the Derby test setup). Might be worth adding so any breakages in the scrollable result sets can be caught in the future.

[Kristian Waagan]

'derby-3397-2a-junit_reg_test.diff' adds two simple JUnit regression tests that fail without the patch and succeeds with it.

Please review, I plan to commit the patch and the tests shortly.

[Kristian Waagan]

Updated affects versions.
The fix should probably go into trunk, then be backported to 10.4 and 10.3.

Downgraded priority to major, as the problem is now well understood, there is at least one workaround (causing performance degradation though) and it mostly affects environments doing pagination.

[Knut Anders Hatlen]

The fix looks correct, and I have verified that the tests expose the bug. +1 to commit.

[Dag H. Wanvik]

Thanks for the help, Kristian, I was travelling the last two days! Test looks good, too.
+1 to commit.

+1 to backport to 10.3 and 10.4 as well.

[Michael Lossos]

Thank you all for fixing this.

+1 for backport

[Kristian Waagan]

Committed 'derby-3397.diff' to trunk with revision 650783 and 'derby-3397-2a-junit_reg_test.diff' to trunk with revision 650786.
Thanks to all who contributed on this issue, especially the users following up :)

The revisions merge cleanly to 10.3 and 10.4, but I will wait some days before I backport them.

[Michael Lossos]

Will these changes be backported to 10.3 and 10.4? We're waiting on our Derby upgrade until a release comes out with this fix. Cheers!

[Kristian Waagan]

I'm re-running the tests, but the regression test didn't build on 10.3. I'm not sure if I have the time to look at that right now, but I plan to backport the fix to both 10.3 and 10.4, and the test to 10.4 in a few hours.
Hopefully it will make it into the upcoming 10.3 update release.

[Michael Lossos]

Great, thank you!

[Kristian Waagan]

Backported the fix and test to 10.4 with revision 652656 and the fix only to 10.3 with revision 652657. I did not backport the test to 10.3 because some code related to the JUnit test framework did not compile. Should be easy to correct, but I want the fix in before the imminent 10.3 update release is produced.
All regression tests ran cleanly for both 10.3 and 10.4.

Resolving issue, leaving it up to the reporter to close when the fix has been confirmed.

[Michael Lossos]

Thanks very much! I will close this out after I've verified it in the next 10.3 release.