Oracle Pagination problem (testCase won't pass)

eugenio · **Joined:** Mon Aug 15, 2005 10:02 am **Posts:** 7

Hi,

Sory for another topic about this but I've been througth this issue for three days by now and can't find a answer in google, hibernate forum or gira. This simple testCase won't pass using Oracle database and Hibernate 3.2.2. Is there any wrong assumptions made here?

Code:

    public void setUp() throws Exception {
        Transaction tx  = HibernateUtil.getSessionFactory().getCurrentSession().beginTransaction();
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        Collection all = session.createQuery("from DataObject").list();
        for(Object o : all) {
            session.delete(o);
        }
        session.flush();
        for(int i=0;i<20;i++) {
            DataObject dto = new DataObject(new Long(i+1L));
            dto.setStringField("D " + (i+1));
            dto.setOtherStringField("Desc " + (i+1));
            if (i<3) {
                dto.setBooleanStringField("N");
            } else {
                dto.setBooleanStringField("S");
            }
            session.save(dto);
        }
        tx.commit();
    }
    
    public void testOraclePaginationError() {        
        // Start a unit of work
        HibernateUtil.getSessionFactory().getCurrentSession().beginTransaction();
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
   List result1,result2;
   //Only happens with some kind of ordering
        String hql = "select p0 from DataObject p0 order by p0.booleanStringField asc";
   Query q = session.createQuery(hql);
        //First page
   q.setFirstResult(0);
   q.setMaxResults(10);
        q.setFetchSize(10);
   result1 = q.list();   
        //Second page
   q = session.createQuery(hql);
   q.setFirstResult(10);
   q.setMaxResults(10);
        q.setFetchSize(10);
   result2 = q.list();   
        for(Object o : result2) {
            log.info("Testing: " + o);
            assertFalse(result1.contains(o)); //wont pass!!!
        }
    }

The pages sholdn't contain the same objects since they are ordered. Examining the generated SQL we can see why. For the first page the SQL looks like this:

Code:

select * from (<QUERY>) where rownum <= X

For the second page the SQL is generated in this form:

Code:

select * from ( select row_.*, rownum rownum_ from ( <QUERY> row_ ) where rownum_ <= X and rownum_ > Y

This disciction leads to the problem described (entities that appear in the first page also appear in subsequent pages). Isn't the case of changing the generated SQL to the second form for every Query that uses the methods setMaxResults/setFirstResult ? Is there any form of doing this "ajustment"?

Any help on this is much appreciated as it's being used in all use cases in a large projetct and we can't afford changing the persistence layer to other framework or use pure JDBC.

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

The SQL looks fine to me, assuming that Oracle doesn't have a "select top" or equivalent. The two most likely reasons for your test failing are
1) Your equals method is wrong, resulting in contains() returning true when it shouldn't,
2) Your order by clause isn't sufficiently deterministic.

I don't know how Oracle sorts groups of rows that are the same according to the order by clause, but if it's not deterministic, then the problem is your order by. Ordering by a column that can only have two values is not going to help too much. Try the test again, using the PK as the order by column (because that's different for every row).

eugenio · **Joined:** Mon Aug 15, 2005 10:02 am **Posts:** 7

Thanks for the response. I've modified the ordeby clause, inserting the primary key as you suggested. The testCase pass now and I can only assume that the reason for that is that queries in Oracle, as you noticed, are not deterministic, as I've assumed.

I think that system users make that assumption too (if they are seeing a row in the first page that row shouldn't be on the second page). So one possible solution would be to include the primary key in every search that can be paged to garantee that the results are always in that order.

But would that work in every case, I mean what about joins, or even tables using the primary key as a business field? In my tests, using the second sintax for fetching the pages always works. Hibernate being a framework, meaning that it should work (handdling mapping OR persistence) as documented for paging, wouldn't be better using a sintax(dialect) that better handle pagination for that specific database?

Reguards.
Eugênio.

swillfah · **Joined:** Wed May 18, 2005 4:50 am **Posts:** 18

Without adding a unique column (like the primary key) to your order by clause you can't expect the results from Oracle to be deterministic.

The order of the rows will depend on many factors like the order of the data in the table segment blocks or the access plan used by Oracle (e.g. table scan versus index scan etc.)

Even if the rows seemed to be ordered correctly at the moment this might change randomly in the future (e.g. when Oracle might choose another execution plan). So only rely on the ordering you specify explicitly (adding something like the primary key).

By the way, since you're probably using the standard isolation level of READ_COMMITTED (and IMHO you should be), you can't be certain that there won't be new rows added (or old ones deleted) between reading the "pages". So you can't be sure that the pagination query won't read duplicates of rows you already read or skip rows that should be there.

monzdrpower · **Joined:** Tue Sep 20, 2005 10:13 am **Posts:** 5

I'v got the same problem.

for the first page setFirstResult(0) main query looks like

Code:

select * from (select rownum, d.* from depts d emp order by name) row_ ...

it is wrong 'cos "order by" order do not equals "rownum" order ...

for the 2nd page (setFirstResult(20)) main query is

Code:

select rownum, row_.* from (select rownum, e.* from emp e order by name) row_ ... 

and works fine

Why the 1sf page query omits "second-level rownum" and produces wrong data? How I can add PK to order by if I need the fullname order?