SELECT round(0.15,1);

result = 0.1

should be 0.2

Linux x85_64

am i missing something?

Note that 0.15 does not have a finite representation in binary, so the floating point number resulting from parsing 0.15 will be either slightly smaller than 0.15 or slightly larger. Your finding indicates the former.

sqlite> select round(0.49999999999999999); 1.0

sqlite> select round(0.4999999999999999); 0.0

Yes, this: https://floating-point-gui.de

The number 0.15 cannot be represented exactly by an IEEE-754 double. The closest approximation is 5404319552844595 * 2^-55 which works out to be 0.1499999999999999944488848768742172978818416595458984375 and that number founds down to 0.1.

I use John Herbster routines created 2001.09.19. I overload round function but maybe it could be part of sqlite. Original file if DecimalRounding_JH1.pas.

These routines round input values to fit as closely as possible to an output number with desired number of decimal fraction digits.

You appear to be missing the thrust of Harald's and Richard's replies. Summarizing them: The results you have reported under "BUG" are not evidence of a bug; they are the correct results. Since they both explained what lies behind what seems to be anomalous to you, I will not replicate that. However, I suggest that you not think of something like "0.15" as representing a binary floating point number. It represents a decimal-radix number for which there is no exact binary representation.

I'm not anonymous from first post. This rounding can be done and other developers have worked on that. Like I said look at John Herbster solution. It could be implemented in sqlite because is not always possible to define overload function.

It could be implemented in sqlite...

Not without a behavior change which which may impact many thousands of applications and countless users. Backwards compatibility is, because of this project's unusually large user base, a top priority. Gratuitous backwards incompatibility is never an option.

If you look at sqlite history changes like that can be implemented with using pragma if this is such a big problem. For me I have a solution for 14 years but I see many asking about this and it is so simple to do.

The fact that the implementation uses IEEE 754 need not imply that SQL users should have to be aware of the intricacies of floating-point representations.

If SQL Fiddle is to be believed, for select round(0.15,1):

• SQL Server returns 0.2
• PostgreSQL returns 0.2
• MySQL returns 0.2
• MariaDB returns 0.2

So SQLite is definitely an outlier in this case.

OP's suggestion of a pragma to introduce mathematical rounding behavior doesn't seem outlandish.

Those other systems likely use (slow) decimal arithmetic to accomplish that.

mathematical rounding

Binary arithmetic is just as mathematical as decimal arithmetic.

You can presumably see why someone entering a decimal number, and then applying a rounding function with precision given as a decimal number in a domain where most other engines (apparently) treat this as a decimal calculation would be surprised by the result though?

This certainly fails the principle of least surprise.

There are excellent reasons why it is done like this, and important project integrity reasons why change is significant and due consideration.

However, I don't think this brushing off of such a problem is entirely without risk.

Perhaps a pragma is the answer. Perhaps a different rounding function. Perhaps a clear explanation about scaling the number before rounding it.

However, for a number presented as a decimal, this is clearly the wrong answer, irrespective of the hidden conversion to binary floating point which explains the reasons.

It's a reason, but a poor excuse.

Once a number has been converted to binary floating point, it's too late. If you want unsurprising decimal rounding, you need to keep the data in some decimal format.

The decimal extension exists, but it only has a minimal set of operations. A decimal_round function would be easy to add, but a decimal_div function would not.

Also other computing tools give here differing results:

Python 3.12.3 (main, Apr 17 2024, 00:00:00) [GCC 14.0.1 20240411 (Red Hat 14.0.1-0)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> round(0.15, 1)
0.1

julia> round(0.15; digits=1)
0.2

 < M A T L A B (R) >
>> round(0.15, 1)

ans =

    0.2000

It is quite unlikely that Matlab and Julia do here (slow) decimal arithmetic, both advertising themselves as high performance compute engines. Rather their binary implementations probably are "smart".

in Julia, for example, the round function has an optional third argument specifying which type of rounding should be performed. This would be an option also for SQLite, perhaps as a loadable extension. This seems preferable over another PRAGMA setting.

What should be the answer to the following two queries?

SELECT round(0.15, 1);
SELECT round(0.1499999999999999944488848768742172978818416595458984375, 1);

Should they give the same answer, or different answers? Should they both return 0.1, or 0.2, or should the first one return 0.2 and the second 0.1.

If you think they should return different answers, then that is a problem because the first arguments to round() in the cases above are just alternative spellings for exactly the same number. For an IEEE754 binary double, the following statement is true:

0.1499999999999999944488848768742172978818416595458984375 == 0.15

For that matter, these are also equivalent:

0.14999999999999999 == 0.15

In a IEEE754 binary double, all of 0.15, 0.14999999999999999, 0.1499999999999999944488848768742172978818416595458984375, and countless other decimal values are represented by the exact same bit pattern: 0x3fc3333333333333

The round() function has no way of knowing what decimal representation for the number the user originally typed in. The only information the round() function has is the binary IEEE754 double representation of that number, 0x3fc3333333333333. Yes, I could modify the round() function so that it renders round(0.15,1) as 0.2. But if I do so, then it would also render round(0.14999999999999999,1) as 0.2 because 0.15 and 0.14999999999999999 are the same number.

So what should round(0.15,1) return? Do you want the truth, or do you want the least surprising answer?

I'm not anonymous from first post but joined this discusion. Yes not all developers knows how numbers are stored in IEEE-754 as you have described. But if you must use them and you must have round function that actually do round as demanded then solution exists.

I use this functions from pascal from John Herbster that are very fast and give me what I need.

https://github.com/vkorshun/vklib/blob/master/DecimalRounding_JH1.pas

If you would look at this you will see and no more explanation is needed.

I confirm that in Python, Matlab, and Julia

python> 0.1499999999999999944488848768742172978818416595458984375==0.15
True

matlab> 0.1499999999999999944488848768742172978818416595458984375==0.15

ans =

  logical

   1

julia> 0.1499999999999999944488848768742172978818416595458984375==0.15
true

Nevertheless Pythons rounds 0.14999... to 0.1, but Matlab and Julia to 0.2 :

python> round(0.1499999999999999944488848768742172978818416595458984375, 1)
0.1

matlab>  round(0.1499999999999999944488848768742172978818416595458984375, 1) 

ans =

    0.2000

julia> round(0.1499999999999999944488848768742172978818416595458984375; digits=1)
0.2

(There are many more compute tools and database systems where this could be tested, these are just three where I have quick access)

While these tools seem to agree on the truth of the equality statement the results of the rounding differs. The "least surprising result" seems to have a "democratic" majority, but this would of course not be binding for SQLite.

Nevertheless Pythons rounds 0.14999... to 0.1, but Matlab and Julia to 0.2

Thanks for uncovering that Stephan.

Frankly, that's horrifying (for MATLAB and Julia).

One intuitively assumes that the "least surprising" result is due to the assumption that round() and similar are actually parsing the decimal representation.

But you seem to have shown that this MATLAB/Julia result is a fudge (from adding episilon to the floating point number first? I actually shudder just to think about it).

I'd prefer a "decimal" option to round.

But I'd hate a lying round that treats 0.14999999... as 0.2 with a passion.

This is not necessarily evidence of fudging.

When using 64-bit binary floating point, 0.1499999999999999944488848768742172978818416595458984375*10.0 evaluates to 1.5 (exactly).

So you'd get the reported results if two-argument round is implemented as "scale by a power of ten; round to integer; scale by the inverse power of ten".

One way to test this might be to check what round(0.00015,4) returns. (0.00014999999999999998685946966947568625982967205345630645751953125*10000.0 evaluates to 1.4999999999999997779553950749686919152736663818359375 rather than 1.5.)

IEEE 754 describes 4 methods for rounding floats to an integer, the default is "Round to nearest". IEEE 754 hardware would support this rounding to integer, and the scale-round-scale approach for rounding to significant digits would be very performant, in spirit closer to the IEEE 754 standard.

According to wikipedia, there are two “round to nearest” methods: Ties to even, and ties away from zero. The default is supposedly round to even.

A tiny bit of experimentation reveals to my surprise that sqlite seems to do round away from zero:

sqlite> select round(2.5,0);
3.0
sqlite> select round(-2.5,0);
-3.0

Is that intentional, or is it merely due to the hardware I am running on? I get the same result on an M1 mac and a linux server running on Inten hardware.

It might be a case of mimicking PostgreSQL, whose two-argument round is documented to round away from zero in case of ties.

Not sure about your application, but if 2 decimals (cents?) you're after, then you may try a scale-round-scale approach.

sqlite> select round(0.15*10,0)/10;
0.2

sqlite> select round(0.14*10,0)/10;
0.1

sqlite> select round(0.15*10,0)/10; 0.2

Now that is very clever.

1. Retains Sqlite tradition.
2. Quickly "converts" N mantissa digits to integer.
3. Quickly "converts" integer back to binary.
4. Produces popular expectation.

Clear thinking!

Sorry to be such a curmudgeon, but barring a mathematical proof firmly based on the IEEE 754 standard and the method of converting a decimal representation of any number to a double, I don't believe this will always yield the expected result. I'd be quite surprised if it does.

Harald, don't you agree that integers have an exact IEEE 754 representation; and that ten times some value produces an integer portion which is ten times the integer portion of the value? Can you be more specific about when the algorithm will yield an unexpected result?

Try rounding 0.575 to two digits.

Harald, don't you agree that integers have an exact IEEE 754 representation;

I do.

and that ten times some value produces an integer portion which is ten times the integer portion of the value?

Not at all, but I don't think you intended to say that. To be precise, the integer portion of 10*1.5 is 15, but ten times the integer portion of 1.5 is 10.

Can you be more specific about when the algorithm will yield an unexpected result?

Thanks to Bo Lindbergh in a sibling comment, I have an example: select round(0.575*100.0,0)/100.0; results in 0.57, where select round(57.5,0)/100.0; returns 0.58. Note that 57.5 has an exact representation as a double, whereas 0.575 does not.

For completeness sake, I did create a branch that contains a new algorithm for round(). The changes on that branch can be seen at https://sqlite.org/src/vdiff?from=56af06fa12104a1f&to=3dec4b35ecd68a4d.

Creating that branch does not imply any commitment to land the change at any point in the future. The branch might land someday. It it might be abandoned.

The new algorithm for rounding:

1. If the next digit is 5-9, round up.

2. If the next digit is 0-3, round down.

3. If the next digit is 4 and the 4 is followed by at least three 9s and only 9s continue all the way out to the 16th significant digit, then round up.

4. Else round down.

The algorithm difference is the addition of step 3. The change only appears in the round() function. A call to "format('%.1f',0.15)" or similar, continues to return "0.1".

Please look at this: https://en.wikipedia.org/wiki/Rounding#:~:text=Rounding%20or%20rounding%20off%20means,expression%20%E2%88%9A2%20with%201.414.

Rounding half up is when digit is 5. You did not look at John Herbster solution. He's brilliant solution was fastest because he uses native round function. He has all possible options but for sqlite round half up would be enough. You did not comment on that and I think is not that bad you just ignore it.

Very well. A new branch uses the Herbster algorithm. https://sqlite.org/src/vdiff?from=6935ac71bad3d36c&to=552b1b106691eddb.

On the new branch we have:

• round(0.1499999999999999944488848768742172978818416595458984375, 1) → 0.2

• round(0.149999999999999966693309261245303787291049957275390625, 1) → 0.1

(another annon here)

possible typo on line 468 func.c: double rSgn; /* Sign of the first first */

likely should be "first argument"?

Thank you!

I predict no more questions about round function. Herbster algorithm is by far the best solution I have seen. It is fast and simple.

Неre it is another floating point arithmetic test sample
3 queries with algebraically same 0.1+0.2-0.3 expression produce 3 different answers:

SQLite version 3.46.0 2024-05-23 13:25:27 (UTF-16 console I/O) Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> select 0.1+0.2-0.3; 5.55111512312578e-17 sqlite> select 0.1+0.2-0.2-0.1; 2.77555756156289e-17 sqlite> select 0.1-0.1+0.2-0.2; 0.0

LibreOffice Calc - is still trustful on this math, it calculated expressions

0.1+0.2-0.3 = 0.0
0.1+0.2-0.2-0.1 = 0.0
0.1-0.1+0.2-0.2 = 0.0

In the diagram below, the vertical line represents the real axis, increasing upwards. IEEE754 binary64 values (hereafter "double values") represent a quantization of the real axis. In the diagram, the red dots are five double values in the vicinity of 0.15. Each double value is marked with both its hex value and the exact decimal meaning of that value. The exact position of the real value 0.15 is also marked.

One can think of each discrete double value not as an exact real number, but rather as a range of real numbers. The angled lines in the diagram attempt to show the range of real numbers that each double value represents.

Here are two of many possible rounding algorithms:

1. Round up if the exact value of the double (the red dot) is at or above the inflection point.

2. Round up if the inflection point is anywhere within the range of real numbers that the double represents.

SQLite version 3.46.0 and earlier implement algorithm 1. The OP for this thread thinks that the algorithm should be 2. Algorithm 2 is implemented by the round-up-2 branch.

arrow from 1,0.3 to 1,5.8 thick
circle at 1,1 fill red width 0.08
circle at 1,2 fill red width 0.08
circle at 1,3 fill red width 0.08
circle at 1,4 fill red width 0.08
circle at 1,5 fill red width 0.08
arrow from 0.7,3.2 to 0.9,3.2 thin
text "0.15" with e at 0.65,3.2
line from 1.1,0.51 to 1.3,1 then to 1.1,1.49 thin
line from 1.1,1.51 to 1.3,2 then to 1.1,2.49 thin
line from 1.1,2.51 to 1.3,3 then to 1.1,3.49 thin
line from 1.1,3.51 to 1.3,4 then to 1.1,4.49 thin
line from 1.1,4.51 to 1.3,5 then to 1.1,5.49 thin
box "0x3fc3333333333335" ljust "0.1500000000000000499600361081320443190634250640869140625" ljust fit with w at 1.3,5 invis
box "0x3fc3333333333334" ljust "0.15000000000000002220446049250313080847263336181640625" ljust fit with w at 1.3,4 invis
box "0x3fc3333333333333" ljust "0.1499999999999999944488848768742172978818416595458984375" ljust fit with w at 1.3,3 invis
box "0x3fc3333333333332" ljust "0.149999999999999966693309261245303787291049957275390625" ljust fit with w at 1.3,2 invis
box "0x3fc3333333333331" ljust "0.1499999999999999389377336456163902767002582550048828125" ljust fit with w at 1.3,1 invis

→ /pikchrshow