Re: [sqlite] VERY weird rounding error
On Jun 17, 2012, at 12:23 PM, Keith Medcalf wrote: > SQLITE_SIGNIFICANT_DIGITS defaults to 14, but you can override it. No matter > what is requested, the maximum number of significant digits is limited to the > specification, and rounding is applied to the remaining bits of the > significand, to round to the specified number of significant digits. FYI, the venerable approach: http://kurtstephens.com/files/p372-steele.pdf ftp://ftp.ccs.neu.edu/pub/people/will/retrospective.pdf http://www.cs.washington.edu/education/courses/cse590p/590k_02au/print-fp.pdf http://www.cesura17.net/~will/Professional/Research/Papers/howtoread.pdf e ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] VERY weird rounding error
Hi all,
Here is a summary of my investigations related to the "0.1 case".
[FYI, 8674146.01 is another example]
The short (and censured :-) version: WTF!? That's just insane!!!
Look at this (sqlite3.c compiled only ONE TIME with default options):
[---
test.js:
var db = new SQLite();
db.exec("select 0.1", function(r){writeln(r)});
---
JSDB under GDB:
(gdb) break sqlite3.c:19917
Breakpoint 1 at 0x57d82d: file sqlite3.c, line 19917.
(gdb) run
Starting program: R:\jsdb/jsdb.exe test.js
[New Thread 21228.0x52f8]
Breakpoint 1, sqlite3VXPrintf (pAccum=0x22e184, useExtended=0,
fmt=0x65323d "g", ap=0x22e1f4 "") at sqlite3.c:19917
19917 realvalue /= 10.0;
(gdb) print ((unsigned char *) &realvalue)[0]@13
$1 = "\000ÐÌÌÌ\fKÃ\017@\000\000Ú"
(gdb) n
19918 exp++;
(gdb) print ((unsigned char *) &realvalue)[0]@13
$2 = "\000Ø£p=\n=1e32 && exp<=350 ){ realvalue /= 1e32; exp+=32; }
while( realvalue>=1e8 && exp<=350 ){ realvalue /= 1e8; exp+=8; }
while( realvalue>=10.0 && exp<=350 ){ realvalue /= 10.0; exp++; }
2) decreasing the max number of required mult.
19910,19911c
if( realvalue>=1e16 && exp<=350 ){ realvalue *= 1e-16; exp+=16; }
if( realvalue>=1e8 && exp<=350 ){ realvalue *= 1e-8; exp+=8; }
if( realvalue>=1e4 && exp<=350 ){ realvalue *= 1e-4; exp+=4; }
if( realvalue>=1e2 && exp<=350 ){ realvalue *= 1e-2; exp+=2; }
if( realvalue>=10.0 && exp<=350 ){ realvalue *= 0.1; exp++; }
I trust you guys, for fixing SQLite smoothly.
A big thank you for your patch Keith.
Keep up the very good work
Etienne
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VERY weird rounding error
As long as the digit to the left of the decimal point is zero, and the digit
immediately following the decimal point is non-zero, then yes. (ie, the same
rules as apply for normalizing the coefficient in binary, where the most
significant binary digit must be a one).
Actually, you can achieve 15 and even 16 significant digits in double precision
floating point if you are very very careful with your numerical methods. It is
astonishingly easy to reduce that to a mere 10 or less digits however. It
really depends on your audience.
You can see this pretty easily. Apply the following patch to the amalgamation.
It allows you to "set" the significant digits via a compiler directive
SQLITE_SIGNIFICANT_DIGITS. All the formatted output calls are requesting
"%[!].16g" as their output format (that is, I have made them all consistent
with the longest precision specified in the code). SQLITE_SIGNIFICANT_DIGITS
defaults to 14, but you can override it. No matter what is requested, the
maximum number of significant digits is limited to the specification, and
rounding is applied to the remaining bits of the significand, to round to the
specified number of significant digits. Then try with various settings for the
pre-processor directive. You will note that you get somewhat irrational
results when significant digits is set at 16. Erroneous results at 15, and
accurate 14 digit results at 14. Setting the number of significant digits to
less than 14 simply limits the output precision (but remains accurate to the
specified number of significant digits). Again, it depends on your audience
and what they expect.
--- 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;
@@ -19878,11 +19881,11 @@
case etGENERIC:
realvalue = va_arg(ap,double);
#ifdef SQLITE_OMIT_FLOATING_POINT
length = 0;
#else
-if( precision<0 ) precision = 6; /* Set default precision */
+if( precision<0 || precision>SQLITE_SIGNIFICANT_DIGITS ) precision =
SQLITE_SIGNIFICANT_DIGITS; /* Set default prec
ision */
if( realvalue<0.0 ){
realvalue = -realvalue;
prefix = '-';
}else{
if( flag_plussign ) prefix = '+';
@@ -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, "%!.16g", 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(&out, "NULL", 4);
}else if( pVar->flags & MEM_Int ){
sqlite3XPrintf(&out, "%lld", pVar->u.i);
}else if( pVar->flags & MEM_Real ){
-sqlite3XPrintf(&out, "%!.15g", pVar->r);
+sqlite3XPrintf(&out, "%!.16g", pVar->r);
}else if( pVar->flags & MEM_Str ){
#ifndef SQLITE_OMIT_UTF16
u8 enc = ENC(db);
if( enc!=SQLITE_UTF8 ){
Mem utf8;
---
() ascii ribbon campaign against html e-mail
/\ www.asciiribbon.org
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Black, Michael (IS)
> Sent: Sunday, 17 June, 2012 07:17
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] VERY weird rounding error
>
> I think I figured out your "14" reference.
>
> What you mean is the number of fractional digits on the coefficient which
> equates to 15 significant digits including the single digit left of the
> decimal point. So your "expected precision" is really "scientific notation
> fractional precision".
>
>
>
> My world makes sense again.
>
>
>
>
>
Re: [sqlite] VERY weird rounding error
No references -- but everything is an estimate. Unlike decimal/BCD (IEEE 854-2008, GDAS, etc) you cannot map binary precision directly to decimal precision. This is especially true since your last bits of precision will suffer as you perform the conversion to decimal. I was always told (and understood) that fast'n'cheap floating point (16-bit) was just as accurate as a slide rule. All formats have some "extra bits" of precision which, like the slide rule, are useful for estimating the rounding of the least significant digit. For this to be true you have to have some extra bits in reserve. Generally speaking though even counting on 14 accurate decimal digits from double precision floating point is pushing the limits of precision. I'd use Quad precision for computation if I really needed accuracy to 14-16 significant digits. That doesn't mean that you can't get 15 or 16 significant digits from double precision, merely that you have to really understand the numerical methods being used to actually be certain that precision has been maintained. Then again, some people were still building things using two digit years in 1990. --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Black, Michael (IS) > Sent: Sunday, 17 June, 2012 06:38 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] VERY weird rounding error > > Do you have a reference for this? I found 3: > > > > Wikipedia says 16 > > http://en.wikipedia.org/wiki/IEEE_754-2008 > > BYU says 15 > > http://www.math.byu.edu/~schow/work/IEEEFloatingPoint.htm > > Oracle says 15-17 > > http://docs.oracle.com/cd/E19957-01/806-3568/ncg_math.html > > > > But I've never heard of "expected precision" and google doesn't come up with > term either -- it either has precision or it doesn't as far as I've ever > heard. And i've never heard of 14 digits before (which wouidn't surprise me > though). > > > > > > Michael D. Black > > Senior Scientist > > Advanced Analytics Directorate > > Advanced GEOINT Solutions Operating Unit > > Northrop Grumman Information Systems > > > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on > behalf of Keith Medcalf [kmedc...@dessus.com] > Sent: Saturday, June 16, 2012 8:11 PM > To: General Discussion of SQLite Database > Subject: EXT :Re: [sqlite] VERY weird rounding error > > > > Ideally, you write your software so that differences in the 15th decimal > > place don't matter to you. If you want two platforms to give identical > > results you use integer arithmetic. It really depends on what kind of > > application you're writing (scientific, financial) or what your numbers > > actually represent. > > This is technically incorrect. Floating Point accuracy is in Decimal Digits, > not Decimal Places (unless, of course, the decimal formatted output is in > "Engineering Notation" in which case Decimal Digits == Decimal Places -- > Engineering Notation means that the number to the left of the decimal point > is 0). > > The "theoretical" accuracy (and the "reasonably expected" accuracy) is: > > Half (2 Bytes) ~ 3.32 > Single (4 Bytes) ~ 7.26 > Double (8 bytes)~15.9 14 > Double extended (10 Byte) ~19.2 18 > Quad ( 16 Bytes) ~34.0 32 > > The value 9990.1 has 5 significant digits. > The output value 0.11 has 15 significant digits, of which the > expected 14 are accurate. > > Etienne, thanks for the thanks. > > --- > () ascii ribbon campaign against html e-mail > /\ www.asciiribbon.org<http://www.asciiribbon.org/> > > > > -Original Message- > > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > > boun...@sqlite.org] On Behalf Of Simon Slavin > > Sent: Saturday, 16 June, 2012 17:33 > > To: General Discussion of SQLite Database > > Subject: Re: [sqlite] VERY weird rounding error > > > > > > On 17 Jun 2012, at 12:06am, Etienne wrote: > > > > > Is there a trick (in the sys. libraries mentioned above, or through the > > win32 C API) for changing the way the FPU handles (long) doubles? > > > > You're at tricky low level detail now. Rounding and truncation modes used > by > > chips which implement IEEE754 are often carefully set by compiler writers > to &
Re: [sqlite] VERY weird rounding error
I think I figured out your "14" reference. What you mean is the number of fractional digits on the coefficient which equates to 15 significant digits including the single digit left of the decimal point. So your "expected precision" is really "scientific notation fractional precision". My world makes sense again. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Black, Michael (IS) [michael.bla...@ngc.com] Sent: Sunday, June 17, 2012 7:38 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] VERY weird rounding error Do you have a reference for this? I found 3: Wikipedia says 16 http://en.wikipedia.org/wiki/IEEE_754-2008 BYU says 15 http://www.math.byu.edu/~schow/work/IEEEFloatingPoint.htm Oracle says 15-17 http://docs.oracle.com/cd/E19957-01/806-3568/ncg_math.html But I've never heard of "expected precision" and google doesn't come up with term either -- it either has precision or it doesn't as far as I've ever heard. And i've never heard of 14 digits before (which wouidn't surprise me though). Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Keith Medcalf [kmedc...@dessus.com] Sent: Saturday, June 16, 2012 8:11 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] VERY weird rounding error > Ideally, you write your software so that differences in the 15th decimal > place don't matter to you. If you want two platforms to give identical > results you use integer arithmetic. It really depends on what kind of > application you're writing (scientific, financial) or what your numbers > actually represent. This is technically incorrect. Floating Point accuracy is in Decimal Digits, not Decimal Places (unless, of course, the decimal formatted output is in "Engineering Notation" in which case Decimal Digits == Decimal Places -- Engineering Notation means that the number to the left of the decimal point is 0). The "theoretical" accuracy (and the "reasonably expected" accuracy) is: Half (2 Bytes) ~ 3.32 Single (4 Bytes) ~ 7.26 Double (8 bytes)~15.9 14 Double extended (10 Byte) ~19.2 18 Quad ( 16 Bytes) ~34.0 32 The value 9990.1 has 5 significant digits. The output value 0.11 has 15 significant digits, of which the expected 14 are accurate. Etienne, thanks for the thanks. --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org<http://www.asciiribbon.org/> > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Simon Slavin > Sent: Saturday, 16 June, 2012 17:33 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] VERY weird rounding error > > > On 17 Jun 2012, at 12:06am, Etienne wrote: > > > Is there a trick (in the sys. libraries mentioned above, or through the > win32 C API) for changing the way the FPU handles (long) doubles? > > You're at tricky low level detail now. Rounding and truncation modes used by > chips which implement IEEE754 are often carefully set by compiler writers to > get the results they want. If you mess with the settings they've chosen, you > can get unexpected results in other apps which were previously working > correctly. > > > It's discovering and caring about this sort of thing that separates out the > professional programmer from the someone who shouldn't be paid for > programming. > > I do appreciate your thanks, by the way. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VERY weird rounding error
Do you have a reference for this? I found 3: Wikipedia says 16 http://en.wikipedia.org/wiki/IEEE_754-2008 BYU says 15 http://www.math.byu.edu/~schow/work/IEEEFloatingPoint.htm Oracle says 15-17 http://docs.oracle.com/cd/E19957-01/806-3568/ncg_math.html But I've never heard of "expected precision" and google doesn't come up with term either -- it either has precision or it doesn't as far as I've ever heard. And i've never heard of 14 digits before (which wouidn't surprise me though). Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Keith Medcalf [kmedc...@dessus.com] Sent: Saturday, June 16, 2012 8:11 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] VERY weird rounding error > Ideally, you write your software so that differences in the 15th decimal > place don't matter to you. If you want two platforms to give identical > results you use integer arithmetic. It really depends on what kind of > application you're writing (scientific, financial) or what your numbers > actually represent. This is technically incorrect. Floating Point accuracy is in Decimal Digits, not Decimal Places (unless, of course, the decimal formatted output is in "Engineering Notation" in which case Decimal Digits == Decimal Places -- Engineering Notation means that the number to the left of the decimal point is 0). The "theoretical" accuracy (and the "reasonably expected" accuracy) is: Half (2 Bytes) ~ 3.32 Single (4 Bytes) ~ 7.26 Double (8 bytes)~15.9 14 Double extended (10 Byte) ~19.2 18 Quad ( 16 Bytes) ~34.0 32 The value 9990.1 has 5 significant digits. The output value 0.11 has 15 significant digits, of which the expected 14 are accurate. Etienne, thanks for the thanks. --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org<http://www.asciiribbon.org/> > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Simon Slavin > Sent: Saturday, 16 June, 2012 17:33 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] VERY weird rounding error > > > On 17 Jun 2012, at 12:06am, Etienne wrote: > > > Is there a trick (in the sys. libraries mentioned above, or through the > win32 C API) for changing the way the FPU handles (long) doubles? > > You're at tricky low level detail now. Rounding and truncation modes used by > chips which implement IEEE754 are often carefully set by compiler writers to > get the results they want. If you mess with the settings they've chosen, you > can get unexpected results in other apps which were previously working > correctly. > > > It's discovering and caring about this sort of thing that separates out the > professional programmer from the someone who shouldn't be paid for > programming. > > I do appreciate your thanks, by the way. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VERY weird rounding error
> Ideally, you write your software so that differences in the 15th decimal > place don't matter to you. If you want two platforms to give identical > results you use integer arithmetic. It really depends on what kind of > application you're writing (scientific, financial) or what your numbers > actually represent. This is technically incorrect. Floating Point accuracy is in Decimal Digits, not Decimal Places (unless, of course, the decimal formatted output is in "Engineering Notation" in which case Decimal Digits == Decimal Places -- Engineering Notation means that the number to the left of the decimal point is 0). The "theoretical" accuracy (and the "reasonably expected" accuracy) is: Half (2 Bytes) ~ 3.32 Single (4 Bytes)~ 7.26 Double (8 bytes)~15.9 14 Double extended (10 Byte) ~19.2 18 Quad ( 16 Bytes) ~34.0 32 The value 9990.1 has 5 significant digits. The output value 0.11 has 15 significant digits, of which the expected 14 are accurate. Etienne, thanks for the thanks. --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Simon Slavin > Sent: Saturday, 16 June, 2012 17:33 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] VERY weird rounding error > > > On 17 Jun 2012, at 12:06am, Etienne wrote: > > > Is there a trick (in the sys. libraries mentioned above, or through the > win32 C API) for changing the way the FPU handles (long) doubles? > > You're at tricky low level detail now. Rounding and truncation modes used by > chips which implement IEEE754 are often carefully set by compiler writers to > get the results they want. If you mess with the settings they've chosen, you > can get unexpected results in other apps which were previously working > correctly. > > > It's discovering and caring about this sort of thing that separates out the > professional programmer from the someone who shouldn't be paid for > programming. > > I do appreciate your thanks, by the way. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VERY weird rounding error
On 17 Jun 2012, at 12:06am, Etienne wrote: > Is there a trick (in the sys. libraries mentioned above, or through the win32 > C API) for changing the way the FPU handles (long) doubles? You're at tricky low level detail now. Rounding and truncation modes used by chips which implement IEEE754 are often carefully set by compiler writers to get the results they want. If you mess with the settings they've chosen, you can get unexpected results in other apps which were previously working correctly. Ideally, you write your software so that differences in the 15th decimal place don't matter to you. If you want two platforms to give identical results you use integer arithmetic. It really depends on what kind of application you're writing (scientific, financial) or what your numbers actually represent. It's discovering and caring about this sort of thing that separates out the professional programmer from the someone who shouldn't be paid for programming. I do appreciate your thanks, by the way. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] VERY weird rounding error
If ever I forget later, I would like to thank you guys (esp. Simon, Dominique, Keith and Jay) for the time you spend on this issue. Your idea of dumping the memory at &realvalue was a very good idea, Simon. You were right: "realvalue" values are already slightly different (2 less significant bytes of the 10-bytes equ. string) after the first iteration (i.e. realvalue*=0.1) of the "while" loop (sqlite3.c:19911), while they are still byte-to-byte identical at sqlite3.c:19908. I use exactly the same byte code (i.e. the same sqlite3.o module), Keith. So I summarize the situation: realvalue * 0.1 = res1 in sqlite3.o linked to shell.o realvalue * 0.1 = res2 in sqlite3.o linked to JSDB - with res1!=res2!! The JSDB win32 executable is built like this: gcc.exe -static-libgcc -o jsdb.exe -mconsole -mwindows -mole obj\*.o obj\jsdb.coff -lole32 -lws2_32 -lodbc32 -loleaut32 -lmapi32 -lcomdlg32 -luuid -lwinmm -static -lstdc++ while I simply build the SQLite exec with "gcc -o SQLite.exe sqlite3.o shell.o". Is there a trick (in the sys. libraries mentioned above, or through the win32 C API) for changing the way the FPU handles (long) doubles? Etienne ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VERY weird rounding error
On Sat, Jun 16, 2012 at 02:00:27PM -0600, Keith Medcalf scratched on the wall: > > Found the problem. > > At sqlite3.c 19883: > if( precision<0 ) precision = 6; /* Set default precision */ > > precision has a value of 15. It should be 14 or less. Don't know > where it is getting this value from however. If by "this value" you mean the 6, that's the default precision for "%f" conversion in the printf() family of functions. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VERY weird rounding error
Found the problem.
At sqlite3.c 19883 if( precision<0 ) precision = 6; /* Set default
precision */
precision has a value of 15. It should be 14 or less. Don't know where it is
getting this value from however.
---
() ascii ribbon campaign against html e-mail
/\ www.asciiribbon.org
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Keith Medcalf
> Sent: Saturday, 16 June, 2012 13:28
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] VERY weird rounding error
>
>
> Are you using the "same machine code" or merely "the same source code"?
>
> The sqlite3 shell.c always asks for the results back converted to text.
>
> Does your "other" shell ask for the results as "text" or as a double?
>
> ...>python
> Python 2.7.3 (default, Apr 10 2012, 23:31:26) [MSC v.1500 32 bit (Intel)] on
> win32
> Type "help", "copyright", "credits" or "license" for more information.
> >>> import apsw
> >>> import apswrow
> >>> import math
> >>> db = apsw.Connection(':memory:')
> >>> cr = db.cursor()
> >>> r1 = cr.execute('select 0.1 as double;').next()
> >>> r2 = cr.execute('select cast(0.1 as text) as textval;').next()
>
> >>> r1
> Row(double=0.1)
>
> >>> type(r1[0])
>
>
> >>> math.frexp(r1[0])
> (0.7628639221191407, 17)
>
> >>> math.frexp(float('0.1'))
> (0.7628639221191407, 17)
>
> >>> r2
> Row(textval=u'0.11')
> >>> type(r2[0])
>
>
> >>> math.frexp(float(r2[0]))
> (0.7628639221191413, 17)
>
> ...>sqlite3d
> SQLite version 3.7.13 2012-06-11 02:05:22
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> select 0.1;
> 0.11
> sqlite> select cast(0.1 as double);
> 0.11
> sqlite> select cast(0.1 as text);
> 0.11
> sqlite> select 0.11;
> 0.11
>
> My conclusion is that the conversion from floating point to text in the
> SQLite database engine is inexact and designed as a convenience function
> only. However, it is consistent. My recommendation would be that if you want
> "text" then use "text" everywhere. If you want floating point, then use
> floating point everywhere. You should not expect that wild conversions
> should produce the results you want.
>
> ---
> () ascii ribbon campaign against html e-mail
> /\ www.asciiribbon.org
>
>
> > -Original Message-
> > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > boun...@sqlite.org] On Behalf Of Etienne
> > Sent: Saturday, 16 June, 2012 11:32
> > To: General Discussion of SQLite Database
> > Subject: [sqlite] VERY weird rounding error
> >
> > Hi Simon,
> >
> > Once again, I really do not care of the accuracy.
> >
> > I KNOW 0.1 CAN NOT BE STORED EXACTLY IN A REAL VARIABLE.
> >
> > I just try to figure out why the SAME CODE (sqlite3 library) with the SAME
> > INPUT gives DIFFERENT RESULTS (no matter how accurate/exact they
> individually
> > are!) when linked to different shells.
> >
> >
> > Etienne
> >
> >
> > - Original message -
> > From: Simon Slavin
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] VERY weird rounding error: details
> > Date: Sat, 16 Jun 2012 18:13:29 +0100
> >
> >
> > On 16 Jun 2012, at 5:41pm, Etienne wrote:
> >
> > > 19913: realvalue *= 0.1;
> >
> > This operation cannot be correctly executed while holding the value in a
> real
> > variable. You will get inconsistent results. Please read the reference I
> > gave earlier for an explanation of why it is pointless trying to get
> perfect
> > accuracy while handling a value of 0.1 in a real variable.
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VERY weird rounding error
Are you using the "same machine code" or merely "the same source code"?
The sqlite3 shell.c always asks for the results back converted to text.
Does your "other" shell ask for the results as "text" or as a double?
...>python
Python 2.7.3 (default, Apr 10 2012, 23:31:26) [MSC v.1500 32 bit (Intel)] on
win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import apsw
>>> import apswrow
>>> import math
>>> db = apsw.Connection(':memory:')
>>> cr = db.cursor()
>>> r1 = cr.execute('select 0.1 as double;').next()
>>> r2 = cr.execute('select cast(0.1 as text) as textval;').next()
>>> r1
Row(double=0.1)
>>> type(r1[0])
>>> math.frexp(r1[0])
(0.7628639221191407, 17)
>>> math.frexp(float('0.1'))
(0.7628639221191407, 17)
>>> r2
Row(textval=u'0.11')
>>> type(r2[0])
>>> math.frexp(float(r2[0]))
(0.7628639221191413, 17)
...>sqlite3d
SQLite version 3.7.13 2012-06-11 02:05:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select 0.1;
0.11
sqlite> select cast(0.1 as double);
0.11
sqlite> select cast(0.1 as text);
0.11
sqlite> select 0.11;
0.11
My conclusion is that the conversion from floating point to text in the SQLite
database engine is inexact and designed as a convenience function only.
However, it is consistent. My recommendation would be that if you want "text"
then use "text" everywhere. If you want floating point, then use floating
point everywhere. You should not expect that wild conversions should produce
the results you want.
---
() ascii ribbon campaign against html e-mail
/\ www.asciiribbon.org
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Etienne
> Sent: Saturday, 16 June, 2012 11:32
> To: General Discussion of SQLite Database
> Subject: [sqlite] VERY weird rounding error
>
> Hi Simon,
>
> Once again, I really do not care of the accuracy.
>
> I KNOW 0.1 CAN NOT BE STORED EXACTLY IN A REAL VARIABLE.
>
> I just try to figure out why the SAME CODE (sqlite3 library) with the SAME
> INPUT gives DIFFERENT RESULTS (no matter how accurate/exact they individually
> are!) when linked to different shells.
>
>
> Etienne
>
>
> - Original message -
> From: Simon Slavin
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] VERY weird rounding error: details
> Date: Sat, 16 Jun 2012 18:13:29 +0100
>
>
> On 16 Jun 2012, at 5:41pm, Etienne wrote:
>
> > 19913: realvalue *= 0.1;
>
> This operation cannot be correctly executed while holding the value in a real
> variable. You will get inconsistent results. Please read the reference I
> gave earlier for an explanation of why it is pointless trying to get perfect
> accuracy while handling a value of 0.1 in a real variable.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VERY weird rounding error
On 16 Jun 2012, at 6:32pm, Etienne wrote: > Once again, I really do not care of the accuracy. > > I KNOW 0.1 CAN NOT BE STORED EXACTLY IN A REAL VARIABLE. I am unsurprised to find that your decimal strings are different after the 11th decimal place because your REAL values are not stored to 15 decimal places, and the calculations done with the numbers stored as REAL are not accurate to a precision of 10^-15. Your number 0.901029468 involves a calculation involving the number 999.901029468 which has 20 decimal digits. Calculations performed on Double-lenght REAL numbers are accurate only to 15 decimal digits. Anything after that is just noise and can be ignored. It doesn't matter what shows, because the programmer should never be showing the number to that much precision. You posted >>> Please note that the "realvalue" variable has identical values at the first >>> loop pass. You have no way of knowing that. You are seeing the value converted into a text string in an attempt to show it in decimal. For all you know, the values are different, but they're being shown as the same text string because the difference is lost after the last digit. There's no simple way to find out where the values are becoming different unless you dump the piece of memory the values are being stored in. Do the calculation realvalue-999 in your code, store the resulting value in a variable, and dump the piece of memory that values is stored in (preferably as binary, but hexadecimal is acceptable). Then look at the results from your two environments and see whether they're the same. Your different maths libraries may do any of the following 1) turn your string '0.1' into different bit patterns, in order to store it as a binary value 2) do the same calculations but get different results because they round the right-hand bits differently 3) turn the same binary value into a different text string, when you ask to see the results in decimal. This can happen in different programming languages, or in the same program compiled by two different compilers, or in the same program compiled by the same compiler for two different platforms, or in the same object code running on two identical platforms except for them having different CPUs, or on identical platforms set to do rounding differently. >From what I see, one of your programs is a C program and the other runs under >JSDB, whatever that is. But you've now introduced another complication >because you're using the binary-to-decimal routines in gdb to show the results >as decimal, so instead of two environments you now have three. Even if >they're both using the same IEEE754 algorithms You have no idea what rounding >mode each library uses. For at least 50 years we've known that if you see a computer spit out a decimal number like 0.1 [lots of zeros here] 82734766 anything after the long line of zeros is rubbish. It's perfectly predictable -- if you understand how your CPU does maths -- but it's not important. Because we know that storing values like 0.1 in a REAL variable will lead to that sort of thing because they cannot be stored accurately so it will happen every time. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VERY weird rounding error
Etienne wrote: > Hi Simon, > > Once again, I really do not care of the accuracy. > > I KNOW 0.1 CAN NOT BE STORED EXACTLY IN A REAL VARIABLE. > > I just try to figure out why the SAME CODE (sqlite3 library) with the SAME > INPUT gives DIFFERENT RESULTS (no matter how accurate/exact they individually > are!) when linked to different shells. > > > Etienne The compiler may not produce strict IEEE-754 floating point computations: trade off between optimizations and strict conformance. Floating registers registers have more bits than double IEEE 754 (80 bit registers, i.e. more accuracy than double 64 bits). So strict conformance to IEEE would be not not only slower but can also be less accurate. Now if you need strict conformance to IEEE-754, you can give options to the compiler. Try compiling with gcc option -ffloat-store for example. There might be other relevant options. Also turn off optimizations (-O0). Some links: http://stackoverflow.com/questions/7295861/enabling-strict-floating-point-mode-in-gcc http://stackoverflow.com/questions/982421/how-to-write-portable-floating-point-arithmetic-in-c/ Regards -- Dominique ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] VERY weird rounding error
Hi Simon, Once again, I really do not care of the accuracy. I KNOW 0.1 CAN NOT BE STORED EXACTLY IN A REAL VARIABLE. I just try to figure out why the SAME CODE (sqlite3 library) with the SAME INPUT gives DIFFERENT RESULTS (no matter how accurate/exact they individually are!) when linked to different shells. Etienne - Original message - From: Simon Slavin To: General Discussion of SQLite Database Subject: Re: [sqlite] VERY weird rounding error: details Date: Sat, 16 Jun 2012 18:13:29 +0100 On 16 Jun 2012, at 5:41pm, Etienne wrote: > 19913: realvalue *= 0.1; This operation cannot be correctly executed while holding the value in a real variable. You will get inconsistent results. Please read the reference I gave earlier for an explanation of why it is pointless trying to get perfect accuracy while handling a value of 0.1 in a real variable. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VERY weird rounding error: details
On 16 Jun 2012, at 5:41pm, Etienne wrote: > 19913: realvalue *= 0.1; This operation cannot be correctly executed while holding the value in a real variable. You will get inconsistent results. Please read the reference I gave earlier for an explanation of why it is pointless trying to get perfect accuracy while handling a value of 0.1 in a real variable. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] VERY weird rounding error: details
Hi all,
The "0.1 case" is still a mystery!
For clarity reasons, I have expanded line 19911 of sqlite3.c (v3.7.13)
19909: while( realvalue>=1e32 && exp<=350 ){ realvalue *= 1e-32; exp+=32; }
19910: while( realvalue>=1e8 && exp<=350 ){ realvalue *= 1e-8; exp+=8; }
19911: while( realvalue>=10.0 && exp<=350 ){ realvalue *= 0.1; exp++; }
19912: while( realvalue<1e-8 ){ realvalue *= 1e8; exp-=8; }
19913: while( realvalue<1.0 ){ realvalue *= 10.0; exp--; }
as:
19909: while( realvalue>=1e32 && exp<=350 ){ realvalue *= 1e-32; exp+=32; }
19910: while( realvalue>=1e8 && exp<=350 ){ realvalue *= 1e-8; exp+=8; }
19911: while( realvalue>=10.0 && exp<=350 )
19912: {
19913: realvalue *= 0.1;
19914: exp++;
19915: }
19916: while( realvalue<1e-8 ){ realvalue *= 1e8; exp-=8; }
19917: while( realvalue<1.0 ){ realvalue *= 10.0; exp--; }
No other modification.
SQLite3.c is compiled (gcc -c -g sqlite3.c) with default options ONE TIME ONLY.
(Env: Int. Core i5, Win7 Pro/32, gcc 4.5, gdb 7.2)
The same object module is linked to shell.o (for SQLite) and to JSDB.
SQLite is then launched under GDB (run NUL "select 0.1;", breakpoint set up
accordingly).
The debugging session looks like:
(...)
(gdb) print realvalue-0
$6 = 0.100582077
(gdb) n
19913 realvalue *= 0.1;
(gdb) print realvalue-0
$7 = 0.100582077
(gdb) n
19914 exp++;
(gdb) print realvalue-
$8 = 0.0102037268
(gdb) n
19913 realvalue *= 0.1;
(gdb) print realvalue-
$9 = 0.0102037268
(gdb) n
19914 exp++;
(gdb) print realvalue-999
$10 = 0.901018099 <--
(gdb)
(...)
JSDB run in the same environment with the matching input:
js>var db = new SQLite();
js>db.exec("select 0.1",function(r){writeln(r)});
Here is a piece of the debugging session (same breakpoint):
(...)
(gdb) print realvalue-0
$4 = 0.100582077
(gdb) n
19913 realvalue *= 0.1;
(gdb) print realvalue-0
$5 = 0.100582077
(gdb) n
19914 exp++;
(gdb) print realvalue-
$6 = 0.0102037268
(gdb) n
19913 realvalue *= 0.1;
(gdb) print realvalue-
$7 = 0.0102037268
(gdb) n
19914 exp++;
(gdb) print realvalue-999
$8 = 0.901029468 <--
(gdb)
(...)
Please note that the "realvalue" variable has identical values at the first
loop pass.
They only start diverging (29468/18099 = +/-60%) from the second pass!
This divergence leads to a rounding error in the second case (JSDB), while
SQLite(shell) properly displays the ("faked") result.
So same input, same byte code... and different results! Who the heck said IT is
determinist?
Does anybody figure out the reason of the difference?
Thanks in advance.
Etienne
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
