db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Michael Lossos (JIRA)" <j...@apache.org>
Subject [jira] Created: (DERBY-3397) Derby and break scrollable result sets? Hibernate Query.setFirstResult and/or setMaxResults
Date Fri, 08 Feb 2008 06:29:08 GMT
Derby and break scrollable result sets? Hibernate Query.setFirstResult and/or

                 Key: DERBY-3397
                 URL: https://issues.apache.org/jira/browse/DERBY-3397
             Project: Derby
          Issue Type: Bug
          Components: JDBC
    Affects Versions:,
         Environment: Derby and, Hibernate 3.2.5
            Reporter: Michael Lossos
            Priority: Critical

I am attempting to upgrade our product from Derby to With all other things
held constant, if I change the derby.jar from 10.2.2 to, 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

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

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 but this also exhibits the

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

Thanks for all the hard work on Derby!

This message is automatically generated by JIRA.
You can reply to this email to add a comment to the issue online.

View raw message