I have a Spring Boot application upgrading to 2.7.0 from 2.6.8.

The application uses an H2 database configured in Oracle compatibility mode for running tests.

It seems the new version of H2 no longer allows the use of the LIMIT clause when in Oracle Compatibility mode. It now throws an exception when trying to use the LIMIT clause during a standard findAll(Pageable pageable) from a Spring Data Repository.

This was not a problem in Spring Boot 2.6.8 and previous versions but seems to be an issue now with the upgrade of H2.

I have created a sample repository that highlights this issue: Sample Repository

Comment From: bclozel

We're aware of the breaking changes in H2. This has been covered in our release notes. I don't think Spring Boot can do anything here as downgrading is not an option for us.

Hopefully this will help you resolve this problem.

Comment From: bradf83

Hi @bclozel,

Thanks for looking into my issue, I did read the release notes and followed the H2 migration guide and did not find a resolution to this specific problem.

I also looked through the H2 issue tracker and did find one issue related to this related issue.

Based on the issue above it seems like maybe LIMIT could be implemented differently when talking to an H2 database. Do you think something could be done in Spring Data JPA to resolve this issue? Should I create an issue similar to this in their issue tracker?

I had a feeling this may not get fixed in Spring Boot but wanted to raise it here due to it happening during a Spring Boot upgrade.

Thanks again.

Comment From: wilkinsona

The limit clause is handled at the Hibernate level by the configured database Dialect. In the case of your example application, it's handled by this code in H2Dialect. As such, I don't think there's anything that can or should be done at the Spring Data level.

As you're running H2 in Oracle compatibility mode, you may want to configure Hibernate to use its OracleDialect:

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.OracleDialect

With this change in place, the test in your sample passes. If this doesn't meet your needs, I think you'll have to raise this with the Hibernate team.

Comment From: bradf83

Thanks @wilkinsona,

I feel a bit silly for not thinking of the dialect, I will see if that helps for my situation.