On Monday, 4 November, 2019 02:10, Jay Kreibich <

[hidden email]> wrote:

>> On Nov 4, 2019, at 2:59 AM, Jay Kreibich <

[hidden email]> wrote:

>>> On Nov 4, 2019, at 2:41 AM, Adrian Sherwin <

[hidden email]> wrote:

>>> The simplest example I have found with x=1 is:

>>> "select round(1.15,1)"

>>> Result: "1.1" (should be 1.2)

>> SQLite uses the IEEE-754 floating point format for real values.

>> In that format, the value “1.1500000…” does not exist.

>> The closest value that can be represented is 1.14999997615814208984375

>> Hence, the rounding.

>OK, no, I’m wrong. Because 1.05 rounds to 1.1, even though the

>representation is 1.0499999523162841796875.

>Well, half wrong. It is because of IEEE-754, but not because of

>representation.

>It would appear the round() function simply uses Banker’s Rounding: when

>given a value that ends in exactly 5, use the previous digit to figure

>out which way to go: even goes up, odd goes down.

>According to Wikipedia, this is the default rounding mode for IEEE-754

>

https://en.wikipedia.org/wiki/Rounding#Round_half_to_even><

https://en.wikipedia.org/wiki/Rounding#Round_half_to_even>

No, SQLite3 does not do half-even rounding.

While the half-even round of 1.15 to 1 decimal place is 1.2, the half-even round of 1.05 to 1 decimal place is 1.0 because 0 is even and 1 is odd.

Rounding the "half" in 1.05 to the nearest even number is 1.0, not 1.1.

To use the example given on that Wikipedia page:

SQLite version 3.31.0 2019-11-03 02:30:02

Enter ".help" for usage hints.

Connected to a transient in-memory database.

Use ".open FILENAME" to reopen on a persistent database.

sqlite> .mode col

sqlite> .head on

sqlite> with x(x) as (values (23.5), (24.5), (-23.5), (-24.5))

...> select x, round(x,0) as round, roundhe(x,0) as roundhe from x;

x round roundhe

---------- ---------- ----------

23.5 24.0 24.0

24.5 25.0 24.0

-23.5 -24.0 -24.0

-24.5 -25.0 -24.0

roundhe(x,n) is a function I wrote myself to as closely as possible do "correct" half-even rounding ... and even that is ineffective in some cases.

SQLite3 implements round(x, n) by checking firstly if there is a fractional part. If there is not, then simply return x. Then see if n==0 and if so, do grade-school 4/5 rounding into a 64-bit integer, then return that converted back to floating point. Otherwise take the value that would be printed if you printed x to n decimal places using the builtin printf function and convert that string back to a floating point value.

--

The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

_______________________________________________

sqlite-users mailing list

[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users