Round diffecrence between MariaDB and SQLite

[Marc Vanzegbroeck]

Hello,

I just found a strange difference in the round()-function between MariaDB and SQlite

If a field 'bedrag' = 26.90 for factuurnr = 20170001
and we run

Code: Select all  Expand view  RUN

select round(bedrag*0.85,2) from kasv for factuurnr = 20170001

the result with SQLite = 22.86, and with MariaDB it is 22.87

22.87 seems to be correct, since it's the same result as in (x)Harbour and foxpro
Does anyone know why, because it's a big problem, since in my program I use sometimes calculations on DBF-files , and sometimes SQL-query's.
It's only correct when I use MariaDB.

[Enrico Maria Giordano]

The result is 22.865, so 22.87 is correct. It looks like a bug in SQLite. As a workaround, you can select bedrag and make the calculation later in your code.

EMG

[Marc Vanzegbroeck]

It's seems to be a SQLite know problem.
From the SQLite-FAQ:

Why does ROUND(9.95,1) return 9.9 instead of 10.0? Shouldn't 9.95 round up?

SQLite uses binary arithmetic and in binary, there is no way to write 9.95 in a finite number of bits. The closest to you can get to 9.95 in a 64-bit IEEE float (which is what SQLite uses) is 9.949999999999999289457264239899814128875732421875. So when you type "9.95", SQLite really understands the number to be the much longer value shown above. And that value rounds down.

This kind of problem comes up all the time when dealing with floating point binary numbers. The general rule to remember is that most fractional numbers that have a finite representation in decimal (a.k.a "base-10") do not have a finite representation in binary (a.k.a "base-2"). And so they are approximated using the closest binary number available. That approximation is usually very close, but it will be slightly off and in some cases can cause your results to be a little different from what you might expect.