Precision issue, Double, Oracle DB

[lefebvrecharlie]

Expected behavior and actual behavior:

Hi,

I have some precision issues after an upgrade to version 3.12.3 and last actual release 3.13.1, all was fine in 3.10.5.

I receive a response from another API to get some amounts, BigDecimal.

In my code, I set the scale to 2 with HALF_UP rounding mode.

In debug the value of myPojo is “1519.9”

myPojo.setValue(value.setScale(2, RoundingMode.HALF_UP).doubleValue());
pojoDao.insert(myPojo)

In the DB the value inserted is “1519.900000....1", the column type is FLOAT.

The DTO result from the other API

The table pojo to insert

The result into database

Thanks in advance :)

Versions:

• jOOQ: 3.12.3, 3.13.1
• Java: Java 11. (Open Jdk 11.0.6.10)
• Database (include vendor): Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production
• OS: Windows 10
• JDBC Driver (include name if inofficial driver): oracle.jdbc.driver.OracleDriver 19.3

[lukaseder]

Thank you very much for your report. We've fixed #7548 in jOOQ 3.11, by switching from binding to Oracle's BINARY_DOUBLE and BINARY_FLOAT APIs, instead of the standard JDBC APIs, this might explain it.

However, do note that when you're using floating point types, you will always get these rounding errors when trying to represent decimal numbers. What is the reason why you're storing monetary amount as FLOAT not as NUMBER?

[callain]

Thank you very much for the rapid answer ! It was specially hard to find for us as our integrations tests suites does not fetch values with the precision loss even if they are stored with a loss.

The main reason is legacy... I guess we need to fix the datatypes in order to upgrade.

[lukaseder]

The main reason is legacy... I guess we need to fix the datatypes in order to upgrade.

You could use a custom data type binding for all FLOAT, BINARY_FLOAT, DOUBLE PRECISION, BINARY_DOUBLE columns that restores binding to default JDBC types:
https://www.jooq.org/doc/latest/manual/code-generation/custom-data-type-bindings

[lukaseder]

... however, even if that works around the issue, the underlying latent problem is still there. IEEE 754 types cannot reliably store decimal values. There are workarounds and heuristics that you could apply: https://en.wikipedia.org/wiki/Decimal_floating_point

But ultimately, the NUMBER type (or BigDecimal in Java) will be the soundest way.

[lefebvrecharlie]

Thanks a lot for your help !