On 12 Apr 2018, at 7:49pm, Thomas Kurz <sqlite.2...@t-net.ruhr> wrote:
> [Simon Slavin wrote]
>
>> A similar thing happens when you specify that a column has affinity of REAL.
>> In both cases, SQLite considers that the CREATE command knows better than
>> whatever specifies the value, and does the conversion. However for the
>> number to be stored the conversion has to be reversible. If SQLite reverses
>> the conversion and doesn't get the original string back it stores the string
>> instead.
>
> I don't think this is actually the case:
>
> create table test (i integer, r real, s text);
> insert into test (i, r, s) values (3.141592653589793238462643,
> 3.141592653589793238462643, 3.141592653589793238462643);
> insert into test (i, r, s) values ('3.141592653589793238462643',
> '3.141592653589793238462643', '3.141592653589793238462643');
> select * from test;
>
> In any case except of actually storing the string to column s, I get the
> stored value truncated to 14 decimals.
From the above data (nice test dataset, by the way):
sqlite> SELECT i,typeof(i) FROM test;
3.14159265358979|real
3.14159265358979|real
sqlite> SELECT r,typeof(r) FROM test;
3.14159265358979|real
3.14159265358979|real
sqlite> SELECT s,typeof(s) FROM test;
3.14159265358979|text
3.141592653589793238462643|text
Where you specified that you wanted a string stored and provided a string in
the INSERT command, SQLite stored a string. In all other cases SQLite had to
handle the value as a number at some point in the storage process, and it
turned it into a floating point value as long as its floating point library can
handle. Since it would have to reduce the precision in this way before doing
any mathematical operation on the resulting value, this counts as 'identical
and reversible'.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
