On October 5, 2011 08:59:14 PDT, Black, Michael (IS) wrote:
Changing the 2 "15g" entries in sqlite3.c to "16g" corrects this
problem. 15 digits is all that is guaranteed but the vast majority
of 16-digit values are representable.
Is this a valid solution? Or are there other side effects?
Before change:
sqlite> create table t (a float);
sqlite> insert into t values(1125899906842624);
sqlite> select * from t;
1.12589990684262e+15
sqlite> select a = cast(a as text) as float from t;
0
After change:
sqlite> create table t(a float);
insert into t values(1125899906842624);
sqlite> select * from t;
1125899906842624.0
sqlite> select a = cast(a as text) as float from t;
1
The IEEE754 double specification (64-bit floats) divides the 64-bits
into 1 sign bit, 11 exponent bits and 52 mantissa bits. However,
there is another implied mantissa bit giving 53 mantissa bits.
In order to compute the number of digits required to represent a 64-
bit IEEE754 double in base 10, we need to solve this equation:
2^53 = 10^x
for x.
The solution is simply:
x = 53 * ln(2) / ln(10)
x = 15.954589770190998
(If you disagree that the implied bit should be considered, you still
have x = 15.653559774527017 which is also greater than 15).
So while a 64-bit IEEE754 float does not represent fully 16 decimal
digits, it does represent more than 15 digits therefore converting to
a 15-digit precision decimal representation and back is guaranteed to
be lossy.
In fact, solving the equation:
2^x = 10^15
for x gives x = 49.828921423310442 which is how many binary bits a 15-
digit decimal number represents. So only 2^49.828921423310442 / 2^53
or 11.1% of all possible IEEE754 64-bit doubles will survive the round-
trip to 15-digit precision decimal text and back unchanged (if you
believe the implied bit should not be counted, that's 22.2%).
-- Kyle
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
