Rounding Error

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Rounding Error

Adrian Sherwin
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)

Here is a script which demonstrates the problem where x=6:

<<<<<

--Temporary table for random numbers

create table _x (R real not null);



--Populate random numbers

with x as ( --10 rows

  select 0 as x union all select 1 union all select 2 union all select 3
union all select 4 union all select 5 union all select 6 union all select 7
union all select 8 union all select 9

)

insert into _x

select round(abs(random()/10000000000000.0),7) as R

from x,x,x,x; --10,000 rows rounded to 7dp



--Round to 6dp and display the rows with rounding errors

select R

  ,round(R,6) as Rounded --Sometimes wrong

  ,round(R*1000000,0)/1000000 as Workarounded --Always correct

from _x

where round(R,6) <> round(R*1000000,0)/1000000;



--Tidy up

drop table _x;

>>>>>


Neither SQL Server nor Oracle exhibit this problem.


Best regards,


Adrian Sherwin
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Rounding Error

Jay Kreibich

> On Nov 4, 2019, at 2:41 AM, Adrian Sherwin <[hidden email]> 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.1500000…” does not exist.

The closest value that can be represented is 1.14999997615814208984375

Hence, the rounding.

  -j

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Rounding Error

Simon Slavin-3
In reply to this post by Adrian Sherwin
On 4 Nov 2019, at 8:41am, 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)

SQL1992 4.4.1  Characteristics of numbers:

" An approximation obtained by truncation or rounding of a numerical
value N for an <approximate numeric type> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Rounding Error

Jay Kreibich
In reply to this post by Jay Kreibich

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

 -j

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Rounding Error

Graham Holden
In reply to this post by Adrian Sherwin
Monday, November 04, 2019, 8:41:48 AM, Adrian Sherwin <[hidden email]> 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.14999997615814208984375, and presumably
it is the floating-point representation that is being rounded, not
the "string" representation.

Graham


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Rounding Error

Keith Medcalf
In reply to this post by Jay Kreibich

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
Reply | Threaded
Open this post in threaded view
|

Re: Rounding Error

Keith Medcalf
In reply to this post by Graham Holden

On Monday, 4 November, 2019 02:16, Graham Holden <[hidden email]> 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.14999997615814208984375, 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.149999999999999911182158 and the next representable double precision floating point number is 1.1500000000000001332267629

--
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
Reply | Threaded
Open this post in threaded view
|

Re: Rounding Error

Keith Medcalf
In reply to this post by Jay Kreibich

On Monday, 4 November, 2019 02:10, Jay Kreibich <[hidden email]> wrote:

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

The representation of 1.05 is 1.0500000000000000444089209 and the next closest representable number is 1.049999999999999822364316

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Rounding Error

Jose Isaias Cabrera-4
In reply to this post by Adrian Sherwin

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users