Pagination with Sorting doesn't work in ORACLE database with Hibernate

Question

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.

did you try oracle12cdialect to see if the sql is different? — gsalem, Jul 07 '22 at 15:26
As stated by @gsalem there is also 12cdialect, Oracle supports LIMIT clause (which is internally translated into nested expression with rownum). — ibre5041, Jul 07 '22 at 15:49
I do believe the generated SQL is correct. would you mind providing test case? — Andrey B. Panfilov, Jul 07 '22 at 16:40
Unfortunately it's not easy to provide a complete example, also because I should provide an ORACLE database because with other database (i.e. hyperDB) it doesn't happen. I didn't try the **oracle12cdialect** yet. I'll test and let you know — Corzar, Jul 08 '22 at 08:53
Tested with oracle12cdialect and still the issue is there. It happens when there are records with the same value in the sort field. If the sort field has unique values everything works well — Corzar, Jul 27 '22 at 13:40

Andrey B. Panfilov · Accepted Answer · 2022-07-09T15:01:31.963

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.

Andrey, you're right. In fact that's the scenario when it happens. I accepted your answer even if the real solution was to create a custom implementation of **PagingAndSortingRepository** where I always add sorting by ID to the pageable object — Corzar, Jul 27 '22 at 14:22

score 0 · Answer 2 · answered Jul 08 '22 at 06:56

There is nothing wrong with the syntax above. It works correctly

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500                    30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL>
SQL> select * from emp
  2  order by hiredate desc;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

14 rows selected.

SQL>
SQL> select row_.*, rownum rownum_ from (
  2    select * from emp
  3    order by hiredate desc
  4  ) row_ where rownum <= 5;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO    ROWNUM_
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20          1
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20          2
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10          3
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30          4
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20          5

5 rows selected.

Perhaps you are referring to getting subsequent pages (2,3,4...) - if that is the case, we'd need to see the query generated for that.

Yes, in the subsequent pages it happens that some records already shown are repeated. The problems happens with the combination of pagination and sorting like this: `select * from ( select row_.*, rownum rownum_ from ( select table_.tablefield1, table_.tablefield2, table_.tablefield3... from table table_ where order by table_tablefield1 desc ) row_ where rownum <= 10) where rownum_ > 5` — Corzar, Jul 08 '22 at 08:59
If the data is changing, then this creates a possibility of repeated records. In fact, if you're adding data faster than you're paging, it feels like you are paging "backwards". In the case, a better option is to "remember" the key of the last item on the current page, and then use that as a starting point for the next page. — Connor McDonald, Jul 11 '22 at 00:01
No, data are stable. The repeated records is caused by the described situation. — Corzar, Jul 11 '22 at 07:15

Pagination with Sorting doesn't work in ORACLE database with Hibernate

2 Answers2