JdbcSQLSyntaxErrorException after upgrade to 3.3.0

[Sax388]

With the upgrade from Spring Boot 3.2.6 to 3.3.0 we're getting errors like these when running our tests on an H2 database:

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "insert into report_config (created,exporter_type,file_name,period_type,tenant_id,updated) values (?,?,?,?,?,?) [*]returning id"; SQL statement:
insert into report_config (created,exporter_type,file_name,period_type,tenant_id,updated) values (?,?,?,?,?,?) returning id [42000-224]

This could potentially be connected to https://stackoverflow.com/questions/78539828/spring-data-jpa-validation-fails-after-update-to-spring-boot-3-3-0.

[wilkinsona]

I don't see the connection between the error that you're seeing and the question on Stack Overflow. The former is a problem reported by H2 when executing an SQL query. The latter is an error reported by Hibernate when parsing an HQL query.

If you would like us to spend some more time investigating, please spend some time providing a complete yet minimal sample that reproduces the problem. You can share it with us by pushing it to a separate repository on GitHub or by zipping it up and attaching it to this issue.

[quaff]

I believe you are using wrong hibernate dialect because H2Dialect will not generate unsupported sql like ... returning id.

[Sax388]

@quaff Indeed I'm using another dialect, as in production there's a postgresql database and I need it to be as close to this as possible. With Spring Boot 3.2.6 everything was fine. I followed the migration guide for Hibernate 6.5 but both resolutions found there still produce the error mentioned above. I got there through the release notes link.

This is the minimal sample you've asked for @wilkinsona , thank you for your swift and clear response!
https://github.com/Sax388/sql-h2-postgres-error-on-upgrade-to-spring-3.3.0

[wilkinsona]

Thanks for the sample, I've reproduced the problem and observed that it does not occur when downgrading Hibernate to 6.4.8.Final:

ext['hibernate.version'] = "6.4.8.Final"

This tells us that the change in behavior is due to a change in Hibernate 6.5.

The SQL dialect for Postgres is generating different SQL and this SQL is now incompatible with H2 running in Postgres compatibility mode.

With Hibernate 6.5, the SQL is the following:

insert into report_config (period_type) values (?) returning id

With Hibernate 6.4, it's the following:

insert into report_config (period_type) values (?)

If it works fine with a real Postgres instance then this is arguably a bug in/limitation of H2's Postgres compatibility mode that Hibernate's changed SQL is triggering. If it doesn't work with a real Postgres instance then I think it's a Hibernate bug.

Either way, I'm afraid that this is out of Spring Boot's control and will have to be dealt with by the maintainers of Hibernate or of H2.