BigDecimal conversion broken in division

[fjakop]

Given a table created as

create table TBL
(
    ID                      NUMBER(38),
    SACHKONTO_NR            VARCHAR2(255),
    UMSATZSTEUER_SCHLUESSEL VARCHAR2(255),
    UMSATZSTEUER_SATZ       NUMBER,
    BETRAG                  NUMBER(15, 2),
    SOLL_HABEN              VARCHAR2(255),
    OE_NR                   VARCHAR2(255),
    BESCHREIBUNG            VARCHAR2(255)
);

we're using Spring Data JPA to select and calculate values from this table

public interface MyEntityRepository extends JpaRepository<MyEntity, Long> {
@Query("select round(sum(ab.betrag) * (1 + min(ab.umsatzsteuerSatz / 100)), 2) as brutto from MyEntity ab " +
            "group by ab.sachkontoNr, ab.oeNr, ab.umsatzsteuerSchluessel, ab.sollHaben, ab.beschreibung " +
            "order by ab.sachkontoNr asc, oeNr asc, sum(abs(ab.betrag)) desc" )
Collection<BigDecimal> selectBrutto();

and some test data

insert into tbl (id, beschreibung, betrag, oe_nr, sachkonto_nr, soll_haben, umsatzsteuer_satz,
                 umsatzsteuer_schluessel)
values (1, 'desc', 100, 'oe', 'sk', 'S', 16, 'us');

which works well with H2 v2.1.214 rendering the expected BigDecimal with stringCache="1.16", intCompact=116, precision=3, scale=2

Upgrading to v2.2.222 renders a different BigDecimal with stringCache="1.00", intCompact=100, precision=3, scale=2 which is obviously wrong.

I've narrowed it down to being related to org.h2.expression.BinaryExpression and maybe #3737 and commit e6ce81e, since replacing the BinaryExpression class with the one from version v2.1.214 produces the expected result.

[katzyn]

There is no NUMBER or VARCHAR2 data types in the SQL Standard and H2. These are non-standard data types from Oracle database.

Modern versions of H2 treat NUMBER without parameters as DECLFOAT(40) for a limited compatibility with Oracle.

But results of your query look like UMSATZSTEUER_SATZ column actually has standard NUMERIC (DECIMAL) data type without parameters:

SELECT CAST(16 AS NUMERIC) / 100, CAST(16 AS NUMBER) / 100;
-- H2 2.1.214:
> 0.16000000000000000000 | 0.16
-- Current H2:
> 0	                 | 0.16

Scale of quotient was changed recently for this data type.

So you need to check how exactly your table was created, because JPA can create it automatically with different column types.

Division is a very unreliable operation in SQL, because both precision and scale of quotient aren't standardized. Usually it is safer to use multiplication by 0.01 in such queries, because scale of product must be a sum of scales of its operands in every standard-compliant database system.

[fjakop]

Right, this table is created by Liquibase, the definition of the column UMSATZSTEUER_SATZ is

<column name="UMSATZSTEUER_SATZ" type="NUMBER"/>

which was working well. Changing it to NUMBER(15,2) makes it work again with current H2 release.

Thanks for pointing that out.