jOOQ bad SQL grammar for new H2

I just updated to Spring Boot 2.7.2 and the new H2 2.1.214.

The jOOQ version is 3.16.6 (pro).

Since then, I get a bad grammar SQL exception with a limit query.

If I understand it correctly, the keyword limit is no longer supported in H2 - instead, FETCH FIRST should be used.

dslContext.select( FOO.fields() ).from( FOO ).limit( 1 )

select "FOO".ID" from "FOO" limit ?

Stacktrace:

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "select ""FOO"".""ID"" from ""FOO"" limit [*]?"; SQL statement:
select "FOO"."ID" from "FOO" 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 jdk.internal.reflect.GeneratedMethodAccessor287.invoke(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:568)
    at net.ttddyy.dsproxy.proxy.ConnectionProxyLogic.performQueryExecutionListener(ConnectionProxyLogic.java:112)
    at net.ttddyy.dsproxy.proxy.ConnectionProxyLogic.access$000(ConnectionProxyLogic.java:25)
    at net.ttddyy.dsproxy.proxy.ConnectionProxyLogic$1.execute(ConnectionProxyLogic.java:50)
    at net.ttddyy.dsproxy.listener.MethodExecutionListenerUtils.invoke(MethodExecutionListenerUtils.java:42)
    at net.ttddyy.dsproxy.proxy.ConnectionProxyLogic.invoke(ConnectionProxyLogic.java:47)
    at net.ttddyy.dsproxy.proxy.jdk.ConnectionInvocationHandler.invoke(ConnectionInvocationHandler.java:25)
    at jdk.proxy2/jdk.proxy2.$Proxy140.prepareStatement(Unknown Source)
    at jdk.internal.reflect.GeneratedMethodAccessor287.invoke(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:568)
    at net.ttddyy.dsproxy.proxy.ConnectionProxyLogic.performQueryExecutionListener(ConnectionProxyLogic.java:112)
    at net.ttddyy.dsproxy.proxy.ConnectionProxyLogic.access$000(ConnectionProxyLogic.java:25)
    at net.ttddyy.dsproxy.proxy.ConnectionProxyLogic$1.execute(ConnectionProxyLogic.java:50)
    at net.ttddyy.dsproxy.listener.MethodExecutionListenerUtils.invoke(MethodExecutionListenerUtils.java:42)
    at net.ttddyy.dsproxy.proxy.ConnectionProxyLogic.invoke(ConnectionProxyLogic.java:47)
    at net.ttddyy.dsproxy.proxy.jdk.ConnectionInvocationHandler.invoke(ConnectionInvocationHandler.java:25)
    at jdk.proxy2/jdk.proxy2.$Proxy140.prepareStatement(Unknown Source)
    at org.quickperf.sql.connection.QuickPerfDatabaseConnection.prepareStatement(QuickPerfDatabaseConnection.java:62)
    at jdk.internal.reflect.GeneratedMethodAccessor287.invoke(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:568)
    at org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy$TransactionAwareInvocationHandler.invoke(TransactionAwareDataSourceProxy.java:238)
    at jdk.proxy2/jdk.proxy2.$Proxy320.prepareStatement(Unknown Source)
    at org.jooq.impl.ProviderEnabledConnection.prepareStatement(ProviderEnabledConnection.java:109)
    at org.jooq.impl.SettingsEnabledConnection.prepareStatement(SettingsEnabledConnection.java:82)
    at org.jooq.impl.AbstractResultQuery.prepare(AbstractResultQuery.java:210)
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:307)
    ... 92 more

Is this a known issue?
Is there a way to rewrite the query?

Solution

This seems to be a frequent misconception about how to use jOOQ with H2 as a test database, so I've written a blog post about jOOQ's stance on this. The feature was also requested on the jOOQ issue tracker as #13895

TL;DR: With jOOQ, please don't use H2's compatibility modes.

Valid configurations

There is only really 1 valid configuration when using H2 as a test database product for a different production database product:

Use jOOQ's SQLDialect.H2 and H2 native, without compatibility mode. This is integration tested by jOOQ.

You might be tempted to:

Use jOOQ's SQLDialect.SQLSERVER and H2 with SQL Server compatibility mode. This is not integration tested by jOOQ (see details below). I don't recommend doing this, because it is likely you'll run into a limitation of H2's compatibility mode that jOOQ assumes is there, because jOOQ thinks it's an actual SQL Server instance.
Use SQLDialect.H2 with H2 in compatibility mode doesn't really make sense, because, well, LIMIT is valid in H2, but not in SQL Server, which supports only TOP or OFFSET .. FETCH. So you're not going to get a good SQL dialect match. See e.g.: https://github.com/h2database/h2database/issues/3537

Some background on compatibility modes and using H2 as a test DB

The assumption in jOOQ is when you use H2, then you use H2 natively (e.g. as an in-memory database, also in production). Using H2 as a simple test database isn't the primary use-case for H2, even if it has seen a lot of popularity in recent years. But why not just use a testcontainers based approach instead, and develop / integration test only with your production RDBMS? The benefits are obvious:

You don't have any such infrastructure problems and test artifacts
You get to use all the vendor specific features, among which table valued functions, table valued parameters, XML/JSON, etc.

The H2 compatibility modes are to make sure your native SQL Server queries work on H2 without any changes. This is useful for purely JDBC based applications. But since you're using jOOQ, and SQLDialect.H2, why keep a compatibility mode around in H2? jOOQ already handles the translation between dialects, if you want to continue using H2 as a test database product. But again, I think your life will be simpler if you're using testcontainers. You can even use it to generate jOOQ code as shown here.