JPQL - Pagination on Oracle Database with Hibernate

In your daily work, you rely on many different libraries, trusting they will serve you well, being perfect piece of code ... do you? ... really?! Then it's time to realize that you are perfectly wrong :) Increasing complexity of code leads to new possibilities of making errors :) Many of them are lurking in the libraries used by you, even if they are used for years by thousands of developers.

Let's find some example. Suppose that we are using JPA, and have an entity named Employee, which contains at least two properties: name and id. Suppose that we want to display all employees ordered by name, and paginated. To fetch them from the underlying database we will need JPQL query like this:

 select e from Employee e order by e.name

We will paginate it using setFirstResult and setMaxResults methods of javax.persistence.Query interface. Now we need a JPA provider and database to make it work, let's choose Hibernate, and Oracle (10+).

At first try everything works perfectly :) - but let's assume that we have employees sharing the same name, for ex. 20 of them having name 'Smith' (identifiers between 1 and 20), 10 having name 'Donovan' (identifiers between 21 and 30) and 10 having name 'Johnson' (identifiers between 31 and 40) - total 40 employees. Let's try to display 5 employees on single page, and see what will happen:

Page 1 - employees having ID: 21, 25, 24, 23, 22 - all having name 'Donovan' - good :)
Page 2 - employees having ID: 26, 25, 24, 23, 22 - all having name 'Donovan' - but 4 of them were already displayed on first page (!)
Page 3 - employees having ID: 31, 35, 34, 33, 32 - here comes the 'Johnson' name - good again :)
Page 4 - employees having ID: 36, 35, 34, 33, 32 - 'Johnson' again, and again 4 of them were already displayed on third page (!!)
Page 5 - employees having ID: 1, 17, 18, 19, 20 - here comes the 'Smith' name - good again :)
and finally the real surprise - Pages 6, 7 and 8 contains same employees - having ID: 16, 17, 18, 19, 20

Don't you think something is wrong here ?! ;) Well, the reason of this strange error is visible when you check SQL queries generated by Hibernate:

select * from (
    select
        employee0_.EMPLOYEE_ID as EMPLOYEE1_0_, employee0_.DATE_OF_BIRTH as DATE2_0_,
        employee0_.EMPLOYEE_NAME as EMPLOYEE3_0_
    from EMPLOYEE employee0_
    order by employee0_.EMPLOYEE_NAME
    ) where rownum <= 5

for the first 5 rows and:

select * from (
    select
        row_.*, rownum rownum_
    from (
        select
            employee0_.EMPLOYEE_ID as EMPLOYEE1_0_, employee0_.DATE_OF_BIRTH as DATE2_0_,
            employee0_.EMPLOYEE_NAME as EMPLOYEE3_0_
        from EMPLOYEE employee0_
        order by employee0_.EMPLOYEE_NAME
    ) row_
    where rownum <= 10)
where rownum_ > 5

for the rows 6 - 10 (and similar for next pages).

What the heck?! - you may say - These beautiful SQL queries are suggested for pagination on Oracle's website - see Tom Kyte's article: On ROWNUM and Limiting Results - sure :) - but the one who implemented it in Hibernate didn't read this article too deeply, skipping this important part:

One important thing about using this pagination query is that the ORDER BY statement should order by something unique. If what you are ordering by is not unique, you should add something to the end of the ORDER BY to make it so.

As you see, correct JPQL query leads to invalid SQL query for Hibernate / Oracle combination, and the error shows himself only for some combinations of data in the database - it is very difficult to spot because of that.

Do you still think that libraries used by you are perfect? :)

PS: This article has been created thanks to one of my Colleagues - Joanna Głowińska - her awesome work on SQL queries in some of our projects lead me to the above thoughts.