Invalid query is successfuly executed by H2 with DB2 mode.

[Volodymyr Lishchynskyi]

Hi there, 

We are using version of H2 db: h2-2.3.232 with mode: DB2.
Here is our config file from Spring Boot Project:

driverClassName: org.h2.Driver
password: <pwd_here>
username: <usr_name_here>
url: jdbc:h2:mem:testdb0;DB_CLOSE_DELAY=-1;MODE=DB2

As the result of such config, we were able to detect weird behaviour. When we are doing  queries using Hibernate + Spring Data JPA where we are having columns in select which are neither added to group by nor wrapped by aggregation function with other columns which are wrapped by aggregation function - they are executed successfully, while on each modern SQL DB this should fail. 

e.g: SELECT COLUMN1, SUM(COLUMN2) FROM TABLE; 
this should fail as COLUMN1 is not neither aggregated nor in GROUP BY clause, however it passed successfully, however while running such query in real DB2, it fails as expected, what causes misleading test results.

Could someone confirm that such issue exists for someone else?

Thanks in advance, 
Volodymyr

[Evgenij Ryazanov]

Hi!

You can't use H2 to check whether your SQL is valid for other database system.

The SQL Standard has optional feature T301, “Functional dependencies”. H2 supports this feature (with minor limitations) and such support cannot be disabled.

Database systems with this feature allow neither aggregated nor grouped columns as long as their values aren't distinct between all rows within every group. That means the same query will fail or not depending on actual data.

It isn't related to your question, but if you use H2 in unit tests with Spring Boot, you also need to disable automatic configuration of testing databases, otherwise your custom JDBC URL with compatibility mode and other settings will be ignored.

[Volodymyr Lishchynskyi]

Gotcha. Thanks for a quick response.

сб, 25 жовт. 2025 р., 03:43 користувач Evgenij Ryazanov <kat...@gmail.com> пише:

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/h2-database/0d28962a-92bf-46b2-8485-04c10dbf2120n%40googlegroups.com.