On Monday, 4 November, 2019 02:10, Jay Kreibich <j...@kreibi.ch> wrote: >> On Nov 4, 2019, at 2:59 AM, Jay Kreibich <j...@kreibi.ch> wrote: >>> On Nov 4, 2019, at 2:41 AM, Adrian Sherwin <sherwia+sql...@gmail.com> 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
