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.4159 function 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.
>
>
>
>
>
> 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.3 2
> Single (4 Bytes) ~ 7.2 6
> 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 99990.1000000001 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 [<thismessage:/>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 <ejlist-sql...@yahoo.fr> 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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
