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
