Hibernate issue with DB2 query during pagination

Hibernate ORM
1

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

2

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