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
