SQLite has a limited-precision floating-point to decimal conversion routine which it uses to print double-precision floating-point values retrieved from a database. As I’ve discovered, its limited-precision conversion results in decimal numbers of 15 significant digits or less that won’t round-trip. For example, if you store the number 9.944932e+31, it will print back as 9.94493200000001e+31.
SQLite also has a limited-precision decimal to floating-point conversion routine, which it uses to convert input decimal numbers to double-precision floating-point numbers for storage in a database. I’ve found that some of its conversions are incorrect — by as many as four ULPs — and that some decimal numbers fail to round-trip because of this; “garbage in, garbage out” as they say.
Incorrect Decimal to Floating-Point Conversions
To find examples of incorrect decimal to floating-point conversions, I wrote a program that generates random decimal strings and converts them using SQLite’s sqlite3AtoF() function (to call sqlite3AtoF(), I had to modify the SQLite source code file sqlite3.c to make it a non-static function). I compared SQLite’s conversion to the correct one, as calculated by David Gay’s strtod() function.
Here are some incorrect conversions I found on a Windows Visual C++ build of SQLite:
|Input||Correct||SQLite||ULPs in error|
Here are some incorrect conversions I found on a Linux build of SQLite:
|Input||Correct||SQLite||ULPs in error|
On Windows, I found conversions that are off by up to four ULPs; on Linux, I could only find conversions that were off by at most one ULP. SQLite on Linux converts more accurately because it uses extended-precision arithmetic, whereas SQLite on Windows uses only double-precision.
Bad Round-Trips Due to Decimal to Floating-Point Conversion
Incorrect decimal to floating-point conversions can prevent decimal numbers from round-tripping. Of the six examples of incorrect conversions on Windows, two caused bad round-trips:
sqlite> create table t1(d real); sqlite> insert into t1 values(1e-23); sqlite> insert into t1 values(8.533e+68); sqlite> insert into t1 values(4.1006e-184); sqlite> insert into t1 values(9.998e+307); sqlite> insert into t1 values(9.9538452227e-280); sqlite> insert into t1 values(6.47660115e-260); sqlite> select * from t1; 1.0e-23 8.533e+68 4.1006e-184 9.99800000000001e+307 9.95384522269999e-280 6.47660115e-260
You can’t tell from the output that the cause is incorrect decimal to floating-point conversion, but I verified that it is: in debug mode, I manually changed SQLite’s double to the correctly converted value, and the correct decimal value was printed by its floating-point to decimal conversion routine.
I found many more examples that I didn’t show. Strangely, each had one of three exponents: -280, -277, or +307. Also strange is that all the bad round-trips were caused by three-ULP errors; if three ULPs can cause an error, why not four?
No Bad Round-Trips on Linux
On Linux, I found no bad round-trips caused by incorrect decimal to floating-point conversions. This is probably because no conversion was off by more than one ULP.
Fixing the Decimal to Floating-Point Conversion Routine
Ideally, all decimal to floating-point conversions in SQLite would be done correctly; however, this would require arbitrary-precision arithmetic. At the least, the Windows build of SQLite could use extended-precision arithmetic so that it can convert as accurately as the Linux build. This would reduce, if not eliminate, the possibility of bad 15-digit or less round-trip conversions.
Fixing the decimal to floating-point conversion routine would have an additional benefit: it would improve the accuracy of a user’s floating-point calculations. SQLite’s decimal to floating-point conversion routine is the first I’ve encountered that produces incorrect results exceeding one ULP (compare to Visual C++, GCC, and GLIBC, for example). I can’t say what impact these large conversion errors have, since I don’t know what kinds of calculations SQLite users — Windows users in particular — do with floating-point numbers.
A Related SQLite Bug
A SQLite bug titled “Insufficient precision of sqlite3_column_text16() for floating point values” addresses the round-trip from the other direction: floating-point to decimal to floating-point. That would require SQLite to return decimal numbers to up to 17 digits, not 15. The bug report calls for changing the hardcoded '15' to '17', but it would require more than that; the conversion routines would have to use higher precision.