[FIXED] SpringBootTest in error with h2 v2.1.214 because of PageRequest

Issue

I have some tests in error after upgrading from Spring boot 2.5.6 to 2.7.3.

For information we use Oracle for the database and h2 for tests.

I have some tests in failure with the following error:

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException

In fact, the version of h2 was 1.4.200 before and is 2.1.214 now and a lot of things seem to have changed. The reason of the error is not always the same according to the test in error. Sometimes it is an error with a table not found (not solved yet), sometimes it is an error with "Values of types "BOOLEAN" and "INTEGER" are not comparable" (solved by updating a query where a comparison was done with a boolean column like this myBoolean = 0 and it has been updated to myBoolean = false) and I also have an error on a query done with a PageRequest.

For this last case, I have a Controller like this:

public Page<MyEntity> doSomething() {
    final Sort sort = Sort.by(Order.desc("column1"));
    final PageRequest pageRequest = PageRequest.of(0, 1000, sort);
    return myEntityRepository.findAll(pageRequest);
}

But I have an error like that:

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "select myentity0_.id as id1_47_, myentity0_.column1 as column1_47_, myentity0_.column2 as column2_47_ from my_table myentity0_ order by myentity0_.column1 desc [*]limit ?"; SQL statement:
select myentity0_.id as id1_47_, myentity0_.column1 as column1_47_, myentity0_.column2 as column2_47_ from my_table myentity0_ order by myentity0_.column1 desc limit ? [42000-214]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:502)
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:477)
    at org.h2.message.DbException.get(DbException.java:223)
    at org.h2.message.DbException.get(DbException.java:199)
    at org.h2.message.DbException.getSyntaxError(DbException.java:247)
    at org.h2.command.Parser.getSyntaxError(Parser.java:898)
    at org.h2.command.Parser.prepareCommand(Parser.java:572)
    at org.h2.engine.SessionLocal.prepareLocal(SessionLocal.java:631)
    at org.h2.engine.SessionLocal.prepareCommand(SessionLocal.java:554)
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1116)
    at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:92)
    at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:288)
    at com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:337)
    at com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:149)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:176)
    ... 205 more

If I change the Controller like this,the test is in success:

public Page<MyEntity> doSomething() {
    List<MyEntity> result = myEntityRepository.findAll();
    return new PageImpl<MyEntity>(result);
}

So It seems that the problem was due by the use of PageRequest.

Do you have an idea please?

Solution

Java persistence libraries are usually tested only with default Regular mode of H2 and may not work well with other modes.

Oracle doesn't support MySQL/PostgreSQL-style LIMIT, and H2 doesn't allow it in Oracle compatibility mode, but some libraries produce LIMIT instead of standard OFFSET / FETCH for H2.

Spring Data JDBC (spring-data-relational) added support of custom compatibility modes of H2 only about a month ago and version 2.4.3 with this fix isn't released yet.

Hibernate ORM 6.*.* should work well, but Hibernate ORM 5.6.* has a known issue: https://hibernate.atlassian.net/jira/software/c/projects/HHH/issues/HHH-15318

You can enable LIMIT in Oracle compatibility mode of H2 as a temporary workaround. To do that, you need to execute the following Java code during initialization of your application:

org.h2.engine.Mode mode = org.h2.engine.Mode.getInstance("ORACLE");
mode.limit = true;

Answered By - Evgenij Ryazanov
Answer Checked By - Clifford M. (JavaFixing Volunteer)