[FIXED] Pagination with Sorting doesn't work in ORACLE database with Hibernate

Issue

I'm using spring jpa with hibernate. When I use the pagination with sorting (typically in tables) the Oracle10gDialect generates the following SQL

select row_.*, rownum rownum_ from ( 
select table_.tablefield1, table_.tablefield2, table_.tablefield3... 
from table table_ where <condition>
order by table_tablefield1 desc 
) row_ where rownum <= 5

According to this explanation, the order by is in that case not considered as the rownum changes the order of the subquery. And in fact I'm experiencing the issue. Everything works well if I don't put any sorting field.

I opened a bug in Hibernate ORM but no feedback since more than 6 months. Anybody can help?

Environment Spring boot 2.2.0, Java 8, Linux, Oracle 19.0.0.0.0

REMARK! This question does not duplicate this one because I can't change the SQL generated by hibernate. Please check the tags before marking as duplicate.

Solution

I do believe the SQLs generated by HBN to get both first and next pages are correct. It is more likely you are actually facing with sort stability issue: if some records has the same value of column you are ordering by, we can say nothing about their order, and oracle can't say as well, to make sort stable you need to add something unique (i.e. id) to order by clause.

select row_.*, rownum rownum_ from ( 
select table_.tablefield1, table_.tablefield2, table_.tablefield3... 
from table table_ where <condition>
order by table_.tablefield1 desc, table_.id 
) row_ where rownum <= 5

You might not facing with similar issue in other DBs because other DBs might use clustered index to support PK.

And yes, "modern syntax" won't solve sort stability issue.

Answered By - Andrey B. Panfilov
Answer Checked By - Marilyn (JavaFixing Volunteer)