Bug: Rounding problem

[VitaliGr]

Hi,

the ROUND-function is not working correctly on rounding the number 34.775 to 2 decimals: SELECT ROUND(34.775, 2) returns normally 34.78, but I get { 'ROUND(34.775, 2)': 34.77 }. It seems the problem can only solved by this workaround: { 'ROUND(34.775 * 100) / 100': 34.78 }.

[neoxpert]

This is not an better-sqlite3 issue but an sqlite3 one in regards how floating point numbers are handled. There is always a certain inaccuracy involved when converting string inputs into a floating point number. For some inputs it may yield the expected result, for most cases it doesn't.

If I enter the exact same command within the latest sqlite3 version it also outputs 34.77 as result (tested with version 3.43.0 on Manjaro). For example with the input of SELECT ROUND(4.535, 2) it outputs 4.54 as I would expect. If I change the input to SELECT ROUND(4.635, 2) the result is 4.63.

[Prinzhorn]

I generally agree with @neoxpert that IEEE 754 does funny things, however in this case:

$ sqlite3
SQLite version 3.40.1 2022-12-28 14:03:47
sqlite> SELECT ROUND(34.775, 2);
34.78

$ sqlite3
SQLite version 3.43.1 2023-09-11 12:01:27
sqlite> SELECT ROUND(34.775, 2);
34.77

:eyes:

So either they fixed or broke it, but this is definitely not a better-sqlite3 issue. Maybe search the SQLite3 forum and report it there after you've bisected which version caused it.

[VitaliGr]

Thank you! The problem seems to appear after the update to better-sqlite3 version 8.6.0. After downgrading the better-sqlite3 version to 8.5.2 the rounding works correctly { 'ROUND(34.775, 2)': 34.78 }.