Hello,
On 2018-05-17 15:07, Stiefsohn, Roman wrote:
Hello,
i found out that SQLite is having problems with converting high numbers from string to
double, located in the function "sqlite3AtoF":
Yes -- it is having problems, however the problems are located in a
compiler which lacks long double support and not in ``sqlite3AtoF''.
Below is a table of input strings, and the returned double values, tested on
Windows 7 64 bit with SQLite version 3.23.1:
Input String: Converted Result
1.7976931348623152e+3081.7976931348623153e+308
1.7976931348623154e+3081.7976931348623157e+308
1.7976931348623155e+3081.7976931348623157e+308
1.7976931348623156e+3081.7976931348623157e+308
1.7976931348623157e+308INF
1.7976931348623158e+308INF
Ok - should be:
...152e+308 => ...151e+308
...154e+308 => ...153e+308
...155e+308 => ...155e+308
...156e+308 => ...155e+308
...157e+308 => ...157e+308
...158e+308 => ...157e+308
Use a compiler supporting long doubles -- results will be more adequate.
Conversion must be done using higher precision then a destination type's
one. There exist two operations (thus a rounding occurs twice), so you
lose one (ln nof ops / ln 2 == 1) bit at least, what leads to not so
accurate results if you are using the same precision as a precision of
the destination type. In a border case:
1.7976931348623158e+308
===
Mantissa (exact value): 1.7976931348623158 ==> 17976931348623158 *
10^-16 == /2 ==> 8988465674311579 * 2 * 10^-16 ==> (0x1.FEEF63F97D79B *
2^53) * 10^-16. The last factor goes to an exponent.
Ten's exponent (approx value, however the most accurate, what is never
occurring): 10^308 * 10^-16 ==> 10^292 ==>
0x402225AF3D53E7C2BCC068B1E... (243 hex digits) ==>
0x1.008896BCF54F9|F0AF301A2C79EB7036... * 2^970 == rounding ==>
0x1.008896BCF54FA * 2^970
Now we have a final number:
(0x1.FEEF63F97D79B * 2^53) * (0x1.008896BCF54FA * 2^970) ==>
0x1.FEEF63F97D79B * 0x1.008896BCF54FA * 2^1023 ==>
1.F|946300C... * 2^1023 == rounding ==> 2 * 2^1023 == ps
==> 2^1024 ==> INF. If there was no more then one additional bit of a
precision then INF would not appear at all and a finite number:
1.F8 * 2^1023 would emerge.
Originally the behavior was discovered when executing a select statement with a
comparison of the DBL_MAX Value (1.7976931348623158e+308 ):
/*
Entry in Table:
Id Value
1 1.1754943508222878e-38
*/
select id, value from table
inner join
(
select 1 as Id, 1.1754943508222878e-38 as Value
) table2
on table.id = table2.id
where table.Value < (table2.Value - 2.22045e-16) or table.Value > (table2.Value
+ 2.22045e-16)
1. Could you include working examples in the future?
2. Where is DBL_MAX?
3. You are adding/subtracting numbers with a difference of 22 in ten's
exponent. Is it intentional? If so, then the WHERE condition can be
reduced to ``0 < -2.22045e-16 or 0 > +2.22045e-16'' => 0 or 0 => 0 =>
NULL => nothing.
è This query would return 1 record, although there is a value with the exact
same value inside the database ( executed with the sqlite c++ api without usage
of prepared parameters)
4. Due to 3. if an above query had been working, it would not have
returned any records/rows.
-- best regards
Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
