Format specifiers are forcing 15 digits of precision, so the default precision
of 6 is being ignored. Here is a patch that fixes the code in the amalgamation
so the default precision of 6 will be used for floating point to text
operations. I don't know if both need to be patched, but this is the
root-cause of the default precision in the conversion routines not having any
effect.
Index: sqlite3.c
==================================================================
--- sqlite3.c
+++ sqlite3.c
@@ -59458,11 +59458,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 +65065,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, "%!g", pVar->r);
}else if( pVar->flags & MEM_Str ){
#ifndef SQLITE_OMIT_UTF16
u8 enc = ENC(db);
if( enc!=SQLITE_UTF8 ){
Mem utf8;
Or in the fossil repository:
D:\Source\sqlite3\src>fossil diff
Index: src/vdbemem.c
==================================================================
--- src/vdbemem.c
+++ src/vdbemem.c
@@ -224,11 +224,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);
Index: src/vdbetrace.c
==================================================================
--- src/vdbetrace.c
+++ src/vdbetrace.c
@@ -119,11 +119,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, "%!g", pVar->r);
}else if( pVar->flags & MEM_Str ){
#ifndef SQLITE_OMIT_UTF16
u8 enc = ENC(db);
if( enc!=SQLITE_UTF8 ){
Mem utf8;
If there was a reason for setting the precision to 15, then perhaps another
SQLITE_DEFAULT_PRECISION could be defined with a default of 6, to permit the
build to override the default in printf.c
--- printf.c
+++ printf.c
@@ -154,10 +154,17 @@
#ifndef SQLITE_PRINT_BUF_SIZE
# define SQLITE_PRINT_BUF_SIZE 70
#endif
#define etBUFSIZE SQLITE_PRINT_BUF_SIZE /* Size of the output buffer */
+/*
+** Default Precision is 6 and may be overridden
+*/
+#ifndef SQLITE_DEFAULT_PRECISION
+#define SQLITE_DEFAULT_PRECISION 6
+#endif
+
/*
** Render a string given by "fmt" into the StrAccum object.
*/
void sqlite3VXPrintf(
StrAccum *pAccum, /* Accumulate results here */
@@ -400,11 +407,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_DEFAULT_PRECISION; /* Set
default precision */
if( realvalue<0.0 ){
realvalue = -realvalue;
prefix = '-';
}else{
if( flag_plussign ) prefix = '+';
---
() 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 14:00
> To: General Discussion of SQLite Database
> Subject: 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 99990.1 as double;').next()
> > >>> r2 = cr.execute('select cast(99990.1 as text) as textval;').next()
> >
> > >>> r1
> > Row(double=99990.1)
> >
> > >>> type(r1[0])
> > <type 'float'>
> >
> > >>> math.frexp(r1[0])
> > (0.7628639221191407, 17)
> >
> > >>> math.frexp(float('99990.1'))
> > (0.7628639221191407, 17)
> >
> > >>> r2
> > Row(textval=u'99990.1000000001')
> > >>> type(r2[0])
> > <type 'unicode'>
> >
> > >>> 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 99990.1;
> > 99990.1000000001
> > sqlite> select cast(99990.1 as double);
> > 99990.1000000001
> > sqlite> select cast(99990.1 as text);
> > 99990.1000000001
> > sqlite> select 99990.1000000001;
> > 99990.1000000001
> >
> > 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 <slav...@bigfraud.org>
> > > To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> > > 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 <ejlist-sql...@yahoo.fr> 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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
