Hibernate 7.1 upgrade, Db2, setMaxResults stopped working

Hibernate ORM
1

Going from versions
hibernate 6.6.36

to versions:
hibernate 7.1.14

The following worked on the prior versions:

    typedQuery.setMaxResults(1).getResultList()

and correctly generated the following:

fetch first 1 rows only

Under the upgraded version, it is now producing the following:

fetch first ? rows only

I noticed if I downgrade to a really old version of Db2Dialect (like 10.5) that this works, but it looks like with the version 11.1 and beyond, it generates the breaking SQL above.

Please let me know if you need any further details.

2

How is that breaking? According to the documentation, DB2 11.1+ supports variables in the fetch first clause and we even test that.
Note that Hibernate ORM 7.1 requires at least DB2 11.1 since older versions are out of support already.

3

Here are some other details…

Some of the app startup logs:
org.hibernate.orm.jdbc - HHH100017: Database:
name: DB2
version: DSN13015
major: 13
minor: 1

org.hibernate.orm.jdbc - HHH100018: Driver:
name: IBM Data Server Driver for JDBC and SQLJ
version: 4.33.31
major: 4
minor: 33
JDBC version: 4.2

org.hibernate.orm.dialect - HHH035001: Using dialect: org.hibernate.dialect.DB2zDialect, version: 12.1

Some settings from the DB itself:
DB2 Rel : 1315
DB2 F.Lvl : V13R1M504
Max ApplC : V12R1M510
ApplCompat: V12R1M510
Cat Level : V13R1M504

The error I am received when using setMaxResults:
org.hibernate.orm.jdbc.error - DB2 SQL Error: SQLCODE=-4743, SQLSTATE=56038, SQLERRMC=null, DRIVER=4.33.31

4

I’m not sure what is happening exactly. The error code indicates that the compatibility level you configured doesn’t allow using this feature.
But at the same time, the documentation already suggests that variables are supported in the fetch first clause in version 12: IBM Documentation

Maybe this is a bug in DB2? I’d suggest you ask the IBM DB2 support about this problem.