Hibernate 6 Upgrade - H2 Query Syntax Issue

Hibernate ORM
1

I am trying to upgrade an existing app from Spring Boot 2.7/Hibernate 5.4 to Spring Boot 3.1.2/Hibernate 6.2.6. Both setups use H2 2.1.214.

Tests that used to work in the original setup are failing. I’ve debugged this to a point that I know it is because of the way a JPQL query is getting translated in SQL. In the working version, the query gets translated as below.

update account_balances set balance_magnitude=balance_magnitude+?,
unreconciled_debit_amount_magnitude=unreconciled_debit_amount_magnitude+?,
unreconciled_credit_amount_magnitude=unreconciled_credit_amount_magnitude+?
where account_id=?

Whereas, with the new combination, the query gets translated as below.

update account_balances set
balance_magnitude=(balance_magnitude+cast(? as
numeric($p,$s))),unreconciled_debit_amount_magnitude=(unreconciled_debit_amount_magnitude+cast(?
as
numeric($p,$s))),unreconciled_credit_amount_magnitude=(unreconciled_credit_amount_magnitude+cast(?
as numeric($p,$s))) where account_id=?

And the error thrown is as below.

org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement
*** expected “long”;

The *_magnitude columns have type DECIMAL(14,2) in DB and BigDecimal in Java. The dialect used is H2Dialect.

The jdbc url is as given below
jdbc:h2:mem:mydb;NON_KEYWORDS=year;MODE=MySQL;DATABASE_TO_LOWER=TRUE;DB_CLOSE_DELAY=-1

What could be the reason?

Thanks.

1 Like
2

I was able to resolve this by using MySQLDialect instead of H2Dialect.

But still curious to know how to make this work with H2Dialect?

3

You should definitely not be using the MySQLDialect when you are using a H2 database. This looks like a bug to me, so please create an issue in the issue tracker(https://hibernate.atlassian.net) with a test case(https://github.com/hibernate/hibernate-test-case-templates/blob/master/orm/hibernate-orm-6/src/test/java/org/hibernate/bugs/JPAUnitTestCase.java) that reproduces the issue.

4

I have the same issue. Did you find a solution?
The MySqlDialect did not solve it for me. Neither bumping to latest Hibernate (6.4.3.Final)
It works if I change the datatype of the entity property to long - but not with the BigDecimal.

5

Hi @mallju, if the issue is still occuring in the latest Hibernate version it might have not been fixed yet. You should not use a Dialect meant for a different database.

Please try to create a reproducer test case and attach it to a new ticket in our issue tracker, like Christian suggested in his previous comment.