Re: [sqlite] Rounding Error

[Jose Isaias Cabrera]

Adrian Sherwin, on Monday, November 4, 2019 03:41 AM, wrote...
>
> Hi,
>
> I would like to report the following as a bug in SQLITE:
>
> The SQLITE "round" function fails to round between 4.1 and 4.6% of numbers
> correctly to x decimal places when held as x+1 decimal places.
>
> The simplest example I have found with x=1 is:
> "select round(1.15,1)"
> Result: "1.1" (should be 1.2)

I asked the same question before, but it is part of the SQLite FAQ [1].  Ihth.

josé

[1] https://www.sqlite.org/faq.html#q16


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Rounding Error

[Keith Medcalf]

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

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

The representation of 1.05 is 1.0500444089209 and the next closest 
representable number is 1.049822364316

Grade-school 4/5 rounding of 1.05 gives 1.1 ...

-- 
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

Re: [sqlite] Rounding Error

[Keith Medcalf]

On Monday, 4 November, 2019 02:16, Graham Holden  wrote:

>This is almost certainly because (according to
>https://www.h-schmidt.net/FloatConverter/IEEE754.html) the number
>"1.15" cannot be expressed exactly in floating-point; the nearest
>expressible number being 1.1497615814208984375, and presumably
>it is the floating-point representation that is being rounded, not
>the "string" representation.

While that may indeed be the single-precision floating point representation of 
1.15, the double precision floating point representation is:

1.149911182158 and the next representable double precision floating 
point number is 1.1501332267629

-- 
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

Re: [sqlite] Rounding Error

[Keith Medcalf]

On Monday, 4 November, 2019 02:10, Jay Kreibich  wrote:
>> On Nov 4, 2019, at 2:59 AM, Jay Kreibich  wrote:
>>> On Nov 4, 2019, at 2:41 AM, Adrian Sherwin  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.150…” does not exist.
>> The closest value that can be represented is 1.1497615814208984375
>> Hence, the rounding.

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

>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
>

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.524.024.0
24.525.024.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

Re: [sqlite] Rounding Error

[Graham Holden]

Monday, November 04, 2019, 8:41:48 AM, Adrian Sherwin 
 wrote:

> Hi,

> I would like to report the following as a bug in SQLITE:

> The SQLITE "round" function fails to round between 4.1 and 4.6% of numbers
> correctly to x decimal places when held as x+1 decimal places.

> The simplest example I have found with x=1 is:
> "select round(1.15,1)"
> Result: "1.1" (should be 1.2)

This is almost certainly because (according to
https://www.h-schmidt.net/FloatConverter/IEEE754.html) the number
"1.15" cannot be expressed exactly in floating-point; the nearest
expressible number being 1.1497615814208984375, and presumably
it is the floating-point representation that is being rounded, not
the "string" representation.

Graham


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Rounding Error

[Jay Kreibich]

> On Nov 4, 2019, at 2:59 AM, Jay Kreibich  wrote:
> 
> 
>> On Nov 4, 2019, at 2:41 AM, Adrian Sherwin  wrote:
>> 
>> Hi,
>> 
>> I would like to report the following as a bug in SQLITE:
>> 
>> The SQLITE "round" function fails to round between 4.1 and 4.6% of numbers
>> correctly to x decimal places when held as x+1 decimal places.
>> 
>> 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.150…” does not exist.
> 
> The closest value that can be represented is 1.1497615814208984375
> 
> Hence, the rounding.

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

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 


 -j

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Rounding Error

[Simon Slavin]

On 4 Nov 2019, at 8:41am, Adrian Sherwin  wrote:

> The simplest example I have found with x=1 is:
> "select round(1.15,1)"
> Result: "1.1" (should be 1.2)

SQL1992 4.4.1  Characteristics of numbers:

" An approximation obtained by truncation or rounding of a numerical
value N for an  T is a value V repre-
sentable in T such that there is no numerical value representable
in T and distinct from that of V that lies between the numerical
value of V and N, inclusive.

If there are more than one such values V then it is implementation-
defined which one is taken. It is implementation-defined which
numerical values have approximations obtained by rounding or trun-
cation for a given approximate numeric type. "

In other words, if you're exactly on the half you can round either way.

SQL is a database management system, not a presentation layer.  If you're 
reformatting data to present it to a human, do it in your favourite programming 
language/library, not in a DBMS.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Rounding Error

[Jay Kreibich]

> On Nov 4, 2019, at 2:41 AM, Adrian Sherwin  wrote:
> 
> Hi,
> 
> I would like to report the following as a bug in SQLITE:
> 
> The SQLITE "round" function fails to round between 4.1 and 4.6% of numbers
> correctly to x decimal places when held as x+1 decimal places.
> 
> 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.150…” does not exist.

The closest value that can be represented is 1.1497615814208984375

Hence, the rounding.

  -j

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users