An IEEE 754 double-precision floating point number, which is what your CPU is using, is unable to represent values 12.7 or 0.35 or 4.445 exactly. That's because your computer works in powers of two, and all three of those numbers also need powers of five in addition to powers of two in order to have a non-repeating representation. Because 12.7 and 0.35 cannot be represented exactly, close approximations are choosen instead:

• 12.7 → 12.699999999999999289457264239899814128875732421875
• 0.35 → 0.34999999999999997779553950749686919152736663818359375
• 4.445 → 4.44500000000000028421709430404007434844970703125

All three of these approximations can be represented as the sum of powers of two.

The first two numbers do not multiple to get the third, however. After rounding to the 64-bit precision available, the product of the first and second numbers comes out as:

• 4.44499999999999939603867460391484200954437255859375

The exact product of the first two numbers is:

• 4.44499999999999946931339422917518945472216707282399885957130565572459346412870218046009540557861328125

I think you've answered another question. Your answer doesn't explain why the result of round(12.7 * 0.35, 2) suddenly changed in some recent versions of sqlite. So the author used the same computer with the same CPU and everything the same except the version of sqlite and had different results. Also, your answer doesn't say what sqlite users should do to make the result of rounding consistent across the different sqlite versions.

I looked at the code and I see sqlite3FpDecode function in 3.44.2 that wasn't found by the 'round' search in the amalgamation sqlite.c in 3.42.0 It seems the code responsible for parsing floating-point values was refactored to support alternate-form-2 flag. Which is now used in roundFunc the function that implements round function.

For floating point substitutions, the alternate-form-2 flag increases the maximum number of significant digits displayed from 16 to 26, forces the display of the decimal point and causes at least one digit to appear after the decimal point.

That was the answer to the question of why the result suddenly changed in version 3.44.2 (however I didn't check in which exact version that was changed).

The answer to the second part (what to do to have consistent round results) it seems to me people can do nothing as this is a bug fix/regression/not backward-compatible change (depends on your point of view).

weirdo12 run the following sql to see how this new alternate mode affects printing floating point values (which is used as the implementation for rounding now)

SELECT 12.7 * 0.35, round(12.7 * 0.35, 2), round(4.445, 2), sqlite_version()
UNION
SELECT printf('%.*f', 16, (SELECT 12.7 * 0.35)), printf('%!.*f', 16, (SELECT 12.7 * 0.35)), printf('%.*f', 16, 4.445), printf('%!.*f', 16, 4.445)

which prints in the nightly build of db browser

4.445			4.44			4.45			3.43.1
4.4449999999999990	4.4449999999999994	4.4450000000000000	4.4450000000000003

anyway it seems like it was a bug in 3.42.0 implementation of the round function. See that the third digit after the point is 4, so the correct rounding to to 2 digit is 4.44 as it is now not 4.45 as it was.

Anton, thank you for taking the time to do a better job of illustrating the issue.

4.445 4.45 4.45 3.41.2 4.4449999999999990 4.4449999999999993 4.4450000000000000 4.4450000000000002

4.445 4.44 4.45 3.44.0 4.4449999999999990 4.4449999999999994 4.4450000000000000 4.4450000000000003

The reason that I noticed the issue was I was comparing results sets from SQLite, SQL Anywhere and PostgreSQL all using the same test data that I imported from a text file.

For the numbers that I used in the example, SQL Anywhere and PostgreSQL both return 4.45.

If I run this on PostgreSQL:

SELECT 12.7 * 0.35, round(12.7 * 0.35, 2), round(4.445, 2), round(4.44499999999999946931339422917518945472216707282399885957130565572459346412870218046009540557861328125, 2)

I get:

4.445 4.45 4.45 4.44

Anyway, It's kinda interesting. For now I will go back to using 3.42 as it's results are consistent with those other databases we support.

I got it. See

SELECT printf('%.*f', 24, 12.7), printf('%!.*f', 24, 12.7), printf('%.*f', 24, 0.35), printf('%!.*f', 24, 0.35)
12.700000000000000000000000	12.69999999999999929	0.350000000000000000000000	0.3499999999999999778

previously 12.7 and 0.35 literals were parsed less precisely and as the result had slightly bigger values which after multiplication produced slightly bigger result that had 5 at the 3rd digit after the point which then correctly rounded to 4.45 in the case of rounding to 2 digits. Now they are parsed more precisely and therefore have slightly lower values which produce the result with 4 at the third position after the point and again correctly rounded to 4.44.

So even though previously it returned more desired results in this case now it returns more precise results in most cases. The only way to make it consistent across the versions is to ask for yet another config/compile time parameter that would parse floating-point literals as before with less precision.

For the hell of it, I gave this query a try:

SELECT printf('%.*f', 24, 12.7 * 0.35), round(printf('%.*f', 24, 12.7 * 0.35), 2), printf('%!.*f', 24, 12.7 * 0.35), round(printf('%!.*f', 24, 12.7 * 0.35), 2), round(12.7 * 0.35, 2), SQLITE_VERSION()

4.444999999999999000000000 4.45 4.444999999999999396038674 4.45 4.45 3.41.2

4.444999999999999000000000 4.44 4.444999999999999397 4.44 4.44 3.44.0

FWIW, compute environments Python, Matlab, and Julia all return 4.44 for their equivalent of SQL round(12.7*0.35, 2). libm has only round to integer, and its round(12.7*0.35*100) returns 444.

Rust returns 4.44 as well.

Now I suspect the following:

SQL engines (PostgreSQL, MySQL, SQL Server, DuckDB) seem to multiply an expression like 12.7*0.35 in software, the result is exactly 4.445, the same as with multiplication on a piece of paper. Rounding to 2 digits after the decimal point gives 4.45. When converting 4.445 to IEEE 754 and then rounding gives also the result 4.45 because the closest IEEE 754 double is slightly larger than 4.445.

Compiled languages and compute environments (FORTRAN, C, Python, Matlab, Julia,...) convert each 12.7 and 0.35 to IEEE 754, and then do the multiplication in hardware (which is much faster than in software). The result is as described in the posts above. The rounding gives 4.44 because the closest IEEE 754 doubles to both 12.7 and 0.35 are slightly less than the decimal values.

SQLite (it's fast isn't it?) seems to do like the latter, i.e. converting every float to IEEE 754 Before evaluating expressions. Earlier than v3.44, according to Anton, "it seems like it was a bug in 3.42.0 implementation of the round function". With v3.44 it is consistent with the approach of compiled languages, Python, etc.