Updates on SecondaryTable cause incorrect cast for BigDecimal

Description

We are currently migrating from Spring Boot 2.7 to 3.x and encountered a problem when combining BigDecimals with SecondaryTables.

The initial insert into the table is fine. An update to this entity then triggers a cast to numeric(38,2) irrespective of the precision and scale values of the data type in the table. If a BigDecimal has for example a value of 3.088 it is then rounded to 3.09.

I have a working tester available at GitHub: https://github.com/legion47T/hibernate-test-case-templates

Under Oracle DB we have a very similar situation with Strings that are truncated due to a cast to varchar with a fixed length of 4000 characters. Unfortunately I can’t get the tester to reproduce it with H2, so I left it out of this report for now.

Relevant log output of tester execution:

merge 
    into
        SECONDARY_TABLE as t 
    using (
        select
            cast(? as bigint) ID,
            cast(? as numeric(38, 2)) SECONDARY_NUMERIC
    ) as s 
        on (t.ID=s.ID) 
    when not matched 
        then insert (ID, SECONDARY_NUMERIC) 
        values
            (s.ID, s.SECONDARY_NUMERIC)  
    when matched 
    and s.SECONDARY_NUMERIC is null 
        then delete  
    when matched 
        then update set
            SECONDARY_NUMERIC=s.SECONDARY_NUMERIC

Activity

Show:

Timo Schwarz December 5, 2024 at 8:23 AM

I managed to register a customized BigDecimalJavaType with overridden getDefaultSqlScale implementation via TypeContributor.

For our case this is a sufficient workaround.

Timo Schwarz December 3, 2024 at 2:02 PM

Under Hibernate 6.2.32 I could trace it down to org.hibernate.sql.ast.spi.AbstractSqlAstTranslator#renderCasted going into the else-branch because the org.hibernate.sql.ast.tree.expression.Expression is not an instance of SqlTypedMappingJdbcParameter. If it were it could carry custom precision and scale values avoiding invalid rounding. The else-branch applies only defaults which seems to be 38 and 2 for precision and scale respectively.

If we were able to override these defaults in our application I would at least have a workaround available. Maybe by registering different defaults for BigDecimal as a BasicType? I haven’t had the time to explore my options here yet…