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;
sqlite> SELECT r,typeof(r) FROM test;
sqlite> SELECT s,typeof(s) FROM test;

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'.

sqlite-users mailing list

Reply via email to