findFirstBy... and Pageable (with pageNum = 0) fail with JDBC parameter value not bound - null (Hibernate 6.6.13 + Informix10Dialect)

Hibernate ORM
1

I’m using Spring Boot 3.4.5 (spring-boot-starter-parent) with Hibernate 6.6.13.Final and hibernate-community-dialects, using Informix:

spring.jpa.database-platform=org.hibernate.community.dialect.Informix10Dialect

My repository looks like this:

@Repository
public interface PacIdRepo extends JpaRepository<PacId, Integer> {

    List<PacId> findAllByUziv(String uziv, Pageable pageable);

    PacId findFirstByUziv(String uziv);
}

Calls like:

PacId pacId = pacIdRepo.findFirstByUziv("someVal");
List<PacId> pacIds = pacIdRepo.findAllByUziv("someVal", PageRequest.of(0, 2));

fail with:

org.hibernate.sql.exec.ExecutionException: A problem occurred in the SQL executor : JDBC parameter value not bound - null
	at org.hibernate.sql.ast.spi.AbstractSqlAstTranslator.lambda$renderFetchPlusOffsetExpressionAsSingleParameter$7(AbstractSqlAstTranslator.java:4793)
	at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.bindParameters(DeferredResultSetAccess.java:207)

I debugged DeferredResultSetAccess.bindParameters(...) and placed a breakpoint at line 207. In both failing cases, the first item in the loop was:

0 = {AbstractSqlAstTranslator$lambda@16594} 
  arg$1 = null
  arg$2 = {AbstractSqlAstTranslator$OffsetReceivingParameterBinder@16595}

It works fine if I replace findFirstByUziv with a native query like:

@Query(value = "SELECT FIRST 1 * FROM PAC_ID p WHERE p.uziv = :uziv", nativeQuery = true)
PacId findFirstByUziv(@Param("uziv") String uziv);

Also, when using PageRequest.of(1, N) (pageNum=1) with pageable queries, the issue does not occur.

This issue appears to be caused by Hibernate always expecting an additional argument for FIRST which, when using 0 as page number or query derivation method like findFirst, remains unset. In Hibernate 5.3.11 this worked fine without problems, and from experience with other projects using databases like PostgreSQL, this usage should be valid.

Is this a mistake on my side or is it a bug? Thanks in advance for any help or insights.

2

Hi, even if there is a bug, the Hibernate team can’t help you with this, because

  • Informix is not a supported database, the dialect is developed by the community, so any errors are up to the community to fix. Usually this is maintained by @VladoKuruc
  • Your using Spring, so we don’t know what it does behind the scenes

If you can re-ask your question without all the Spring stuff, we might be able to help with the pagination stuff, but I don’t know how Spring uses Hibernate APIs.

3

Thank you very much for answer.
Would it be possible to direct me to where to post such a question for the community about Informix dialect ?
I know there is hibernate/hibernate-orm · Discussions · GitHub but I’m not sure if questions about the community package belong there since it’s still the Hibernate orm repository.

4

I had hoped @VladoKuruc would maybe respond here since I mentioned him, but maybe he is busy right now. He is the “maintainer” of the dialect, so ask him through whatever channels you can find. IMO, asking here is ok. Just wanted to respond something so you know that you’re not being ignored, but rather the Hibernate team can’t help you with that in particular.
Also, removing all the Spring related parts would help as well, then the Hibernate team could maybe help you from a general perspective i.e. on the non-Informix parts of the question.

5

Hi, I’m busy right now. I can look at it later. As Chris says, a minimalist example without Spring would be helpful. If that’s not possible, then at least a minimal one created via Spring Initializr: [Spring Initializr]

6

You should use org.hibernate.community.dialect.InformixDialect instead of org.hibernate.community.dialect.Informix10Dialect