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.

Comment From: 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.

Comment From: quaff

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

Comment From: 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

Comment From: 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.

Comment From: quaff

@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

@Sax388 Why not let Hibernate choose the right dialect? IMO, you shouldn't specify dialect if the database is well-known.

Comment From: Sax388

@quaff Thank you for your support. I didn't quite get you. I intentionally chose to use

      driverClassName: org.h2.Driver
      url: jdbc:h2:mem:testdb;MODE=PostgreSQL;DEFAULT_NULL_ORDERING=HIGH;NON_KEYWORDS=VALUE

to make it behave more like the well-known database in production (there were some issues, not detected by the tests before). We're only using H2 for our tests. I think it's time to ditch it and move on to using a full postgresql database e.g. inside testcontainers. What do you think?

EDIT: I feel a little dumb now, but https://github.com/h2database/h2database/issues/3962#issuecomment-2141234915 told me there was a bad configuration right above the properties mentioned above. Without these it's actually working again.

Comment From: quaff

@quaff Thank you for your support. I didn't quite get you. I intentionally chose to use

yaml driverClassName: org.h2.Driver url: jdbc:h2:mem:testdb;MODE=PostgreSQL;DEFAULT_NULL_ORDERING=HIGH;NON_KEYWORDS=VALUE

to make it behave more like the well-known database in production (there were some issues, not detected by the tests before). We're only using H2 for our tests. I think it's time to ditch it and move on to using a full postgresql database e.g. inside testcontainers. What do you think?

EDIT: I feel a little dumb now, but h2database/h2database#3962 (comment) told me there was a bad configuration right above the properties mentioned above. Without these it's actually working again.

Sorry, I thought you are manually set hibernate dialect, It's MODE=PostgreSQL in jdbc url make hibernate detect H2 as PostgreSQL, I would like suggest you use H2 for unit tests and introduce testcontainers to use real PostgreSQL in integration tests.

EDIT: you are manually set hibernate dialect indirectly by spring.jpa.database=postgresql, not caused by MODE=PostgreSQL.

Comment From: guilhermec-costa

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.

@wilkinsona really appreciate your help. I was getting the exact same error on trying to insert data into a user table like the following: insert into users (email, login, password, user_role) values (?, ?, ?, ?) returning id;

The problem was the "returning id". I just downgraded hibernate and overwrote the version from the one installed from spring jpa. It worked. Thanks again!