# Hibernate issue with DB2 query during pagination

**URL:** <https://discourse.hibernate.org/t/hibernate-issue-with-db2-query-during-pagination/7836>\
**Category:** Hibernate ORM\
**Created:** [June 17, 2023, 3:29pm UTC](https://discourse.hibernate.org/t/hibernate-issue-with-db2-query-during-pagination/7836 "2023-06-17T15:29:50Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Nilesh\_Saple](https://avatars.discourse-cdn.com/v4/letter/n/a587f6/32.png) [@Nilesh\_Saple](https://discourse.hibernate.org/u/Nilesh_Saple)\
**Post date:** [June 17, 2023, 3:29pm UTC](https://discourse.hibernate.org/t/hibernate-issue-with-db2-query-during-pagination/7836/1 "2023-06-17T15:29:50Z")

</div>

Hello Team,

Jar - hibernate-core-5.6.14.Final.jar  
DB2 Version 12.  
using Spring Data JPA for Pagination

below Spring Data JPA statement with offset \> 0 causes SqlSyntaxErrorException.  
pagination = repository.findAll(PageRequest.of(offset, pageSize));

Hibernate created SQL:   
select \* from ( select inner2\_.\*, rownumber() over(order by order of inner2\_) as rownumber\_ from ( select column\_list… from Table\_Name fetch first 10 rows only ) as inner2\_ ) as inner1\_ where rownumber\_ \> 5 order by rownumber\_  
and it gives below exception DB2 SqlSyntaxErrorException.  
org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet  
…  
…  
Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-199, SQLSTATE=42601, SQLERRMC=OF;ROWS \* AT YEAR YEARS MONTH MONTHS DAY DAYS HOUR HOURS MINUTE, DRIVER=4.24.92

when I copied generated sql in DB2 client and replaced ‘**over(order by order of inner2\_)**’ with ‘**over()**’ pagination works perfectly fine.

the bug in class org.hibernate.dialect.DB2Dialect where inner sql is appended in outer sql. and outer sql has problem.

here is org.hibernate.dialect.DB2Dialect class snippet:

public String processSql(String sql, RowSelection selection) {  
return LimitHelper.hasFirstRow(selection) ? “select \* from ( select inner2\_.\*, **rownumber() over(order by order of inner2\_)** as rownumber\_ from ( " + sql + " fetch first " + this.getMaxOrLimit(selection) + " rows only ) as inner2\_ ) as inner1\_ where rownumber\_ \> " + selection.getFirstRow() + " order by rownumber\_” : sql + " fetch first " + this.getMaxOrLimit(selection) + " rows only";  
}

If the code replaced ‘**over(order by order of inner2\_)**’ with ‘**over()**’ it will work.

Also, I came across the same post on old website - [Hibernate Community • View topic - Hibernate issue with DB2 query during pagination](https://forum.hibernate.org/viewtopic.php?p=2491288)

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [June 19, 2023, 8:02am UTC](https://discourse.hibernate.org/t/hibernate-issue-with-db2-query-during-pagination/7836/2 "2023-06-19T08:02:25Z")

</div>

Your `PageRequest` simply misses a `Sort`. Paginating without ordering makes no sense.