SpringBoot JPQL with Pageable after H2 upgrade results in SQLGrammarException

In #30714 H2 was upgraded.

Spring Boot: 2.7.0 uses: com.h2database:h2:2.1.212

In our case this created an issue:

org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement; SQL [select someentry0_.id as id1_0_, someentry0_.created_on as created_2_0_, someentry0_.failure_count as failure_3_0_, someentry0_.json_data as json_dat4_0_, someentry0_.mgs_system_id as mgs_syst5_0_, someentry0_.service_name as service_6_0_, someentry0_.status as status7_0_, someentry0_.updated_on as updated_8_0_ from some_data someentry0_ where someentry0_.status=? limit ?]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement

    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:259)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:233)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:551)
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:152)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:145)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215)
...
org.springframework.orm.jpa.SharedEntityManagerCreator$DeferredQueryInvocationHandler.invoke(SharedEntityManagerCreator.java:406)
    at com.sun.proxy.$Proxy283.getResultList(Unknown Source)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution$PagedExecution.doExecute(JpaQueryExecution.java:179)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:90)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:156)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:144)
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:137)
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:121)
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:159)
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:138)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:80)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)
...
...
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "SELECT SOMEENTRY0_.ID AS ID1_0_, SOMEENTRY0_.CREATED_ON AS CREATED_2_0_, SOMEENTRY0_.FAILURE_COUNT AS FAILURE_3_0_, SOMEENTRY0_.JSON_DATA AS JSON_DAT4_0_, SOMEENTRY0_.MGS_SYSTEM_ID AS MGS_SYST5_0_, SOMEENTRY0_.SERVICE_NAME AS SERVICE_6_0_, SOMEENTRY0_.STATUS AS STATUS7_0_, SOMEENTRY0_.UPDATED_ON AS UPDATED_8_0_ FROM SOME_DATA SOMEENTRY0_ WHERE SOMEENTRY0_.STATUS=? LIMIT[*] ?"; SQL statement:
select someentry0_.id as id1_0_, someentry0_.created_on as created_2_0_, someentry0_.failure_count as failure_3_0_, someentry0_.json_data as json_dat4_0_, someentry0_.mgs_system_id as mgs_syst5_0_, someentry0_.service_name as service_6_0_, someentry0_.status as status7_0_, someentry0_.updated_on as updated_8_0_ from some_data someentry0_ where someentry0_.status=? limit ? [42000-202]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:502)
...

Repository example:

package com.org.app.repository;

import com.org.app.entity.SomeEntry;
import com.org.app.entity.SomeStatus;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Set;

@Repository
public interface SomeRepository extends JpaRepository<SomeEntry, Long> {

// works
  List<SomeEntry> findAllByStatus(SomeStatus status);

// does not work, SomeStatus is ENUM - example: findAllbyStatus(status, Pageable.ofSize(5)) 
  Page<SomeEntry> findAllByStatus(SomeStatus status, Pageable pageable);
}

If i specifically revert h2 dependency back to: com.h2database:h2:1.4.200 the issue does not appear. This issue with SB + JPQL appears since com.h2database:h2:2.0.202 Issue appears when using the keyword limit in the generated JPQL statement.

Example executed at com.zaxxer.hikari.pool.ProxyConnection#prepareStatement(java.lang.String) not working: delegate.prepareStatement("select someentry0_.id from some_data someentry0_ limit ?") not working: delegate.prepareStatement("select someentry0_.id from some_data someentry0_ limit 5") working: delegate.prepareStatement("select someentry0_.id from some_data someentry0_")

Comment From: wilkinsona

Thanks for the report. Unfortunately, this is out of Spring Boot's control. Remaining on H2 1.4.x was not an option due to the security vulnerabilities that it contains. The workaround described in https://groups.google.com/g/h2-database/c/yxnv64Ak-u8 may be of interest. If that doesn't help and you need to use H2, please follow up with the Hibernate team. They may be able to improve their H2 dialect in Hibernate 5.x, back porting some of the improvements that have already been made in 6.0

Comment From: SpiReCZ

Thanks for a quick response with your insight. I created an issue here: https://hibernate.atlassian.net/browse/HHH-15318