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

[sqlite] 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()/10.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*100,0)/100 as Workarounded --Always correct

from _x

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



--Tidy up

drop table _x;

>


Neither SQL Server nor Oracle exhibit this problem.


Best regards,


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

[sqlite] Rounding Error ,,, Last Patch was incorrect (Sorry)

[Keith Medcalf]

That last patch was a bad workaround -- please ignore it.  

The et_getdigit function does count the number of significant digits returned.  
However, it has a wrong boundary.  Limiting the number of significant digits is 
more correct than changing the output precision.  So, the following changes 
(against the amalgamation) will always use and round to the same number of 
significant digits specified by a preprocessor macro SQLITE_SIGNIFICANT_DIGITS, 
which defaults to 14.  The rounding function now works correctly again (I broke 
it under certain circumstances in the other patch).  Also, all output format 
functions use the default specified significant digits and round equally.  If 
you use a specific format in SQL printf function, it will behave properly, and 
if you do not specify a precision or exceed the number of significant digits, 
the results will be correct.  Output in the vdbe code is unchanged (only user 
value I/O is affected by removing the precision specifier from the printf's in 
vdbemem.c).

--- sqlite3.c
+++ sqlite3.c
@@ -19594,17 +19594,20 @@
 ** Example:
 ** input: *val = 3.14159
 ** output:*val = 1.4159function return = '3'
 **
 ** The counter *cnt is incremented each time.  After counter exceeds
-** 16 (the number of significant digits in a 64-bit float) '0' is
-** always returned.
+** SQLITE_SIGNIFICANT_DIGITS '0' is always returned.  Default to 14
+** digits for IEEE 754 Floating Point
 */
+#ifndef SQLITE_SIGNIFICANT_DIGITS
+#define SQLITE_SIGNIFICANT_DIGITS 14
+#endif
 static char et_getdigit(LONGDOUBLE_TYPE *val, int *cnt){
   int digit;
   LONGDOUBLE_TYPE d;
-  if( (*cnt)++ >= 16 ) return '0';
+  if( (*cnt)++ >= SQLITE_SIGNIFICANT_DIGITS ) return '0';
   digit = (int)*val;
   d = digit;
   digit += '0';
   *val = (*val - d)*10.0;
   return (char)digit;
@@ -59458,11 +59461,11 @@
   */
   if( fg & MEM_Int ){
 sqlite3_snprintf(nByte, pMem->z, "%lld", pMem->u.i);
   }else{
 assert( fg & MEM_Real );
-sqlite3_snprintf(nByte, pMem->z, "%!.15g", pMem->r);
+sqlite3_snprintf(nByte, pMem->z, "%!g", pMem->r);
   }
   pMem->n = sqlite3Strlen30(pMem->z);
   pMem->enc = SQLITE_UTF8;
   pMem->flags |= MEM_Str|MEM_Term;
   sqlite3VdbeChangeEncoding(pMem, enc);
@@ -65065,11 +65068,11 @@
   if( pVar->flags & MEM_Null ){
 sqlite3StrAccumAppend(, "NULL", 4);
   }else if( pVar->flags & MEM_Int ){
 sqlite3XPrintf(, "%lld", pVar->u.i);
   }else if( pVar->flags & MEM_Real ){
-sqlite3XPrintf(, "%!.15g", pVar->r);
+sqlite3XPrintf(, "%!g", pVar->r);
   }else if( pVar->flags & MEM_Str ){
 #ifndef SQLITE_OMIT_UTF16
 u8 enc = ENC(db);
 if( enc!=SQLITE_UTF8 ){
   Mem utf8;

I'll send the canonical source patches shortly.



---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org




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