Precision Error in log10(100) on x86 32-bit Systems

Floating-Point Inaccuracy in log10(100.0) During func7-pg-181 Test

The func7-pg-181 test in SQLite’s test suite fails on x86 32-bit systems when calculating log10(100.0) with a formatted output of %.30f. The test expects the result 2.000000000000000000000000000000, but on 32-bit x86 architectures, the actual result is 1.999999999999999000000000000000. This discrepancy arises from differences in floating-point precision handling between architectures and the implementation details of SQLite’s log10() function. The issue is isolated to 32-bit x86 systems, with successful test execution on x86_64, ARM32, AArch64, and PPC64LE platforms. The root cause involves the interaction between SQLite’s reliance on the C standard library’s log() function for computing logarithms, the IEEE-754 binary64 (double-precision) representation of floating-point numbers, and architectural variations in floating-point computation.

Architectural Differences in Floating-Point Computation and Logarithm Implementation

The failure stems from the cumulative effects of three interrelated factors:

1. Approximation Errors in Logarithmic Functions: SQLite’s log10() implementation prior to version 3.41.0 relied on the formula log10(x) = log(x)/log(10), where log() is the natural logarithm function provided by the C standard library (libm). This approach introduces two layers of approximation:

   • The inherent limitations of the log() function’s accuracy in libm.
   • The division by log(10), which is itself an irrational number approximated to machine precision.
     These compounded approximations result in a small error that becomes significant when formatting the output to 30 decimal places.

2. 32-bit x86 Floating-Point Register Behavior: The x86 32-bit architecture historically uses x87 FPU registers, which compute intermediate values in 80-bit extended precision before rounding to 64-bit doubles. This can lead to discrepancies compared to 64-bit architectures that use SSE instructions for floating-point operations, which operate strictly on 64-bit values. The extra precision in intermediate calculations on 32-bit systems alters the rounding behavior, causing the result of log10(100.0) to be slightly less than 2.0 when truncated to 64 bits.

3. Strict Test Expectation of Exact Decimal Representation: The test func7-pg-181 uses the format() function to convert the floating-point result to a string with 30 decimal places. The test expects an exact match to 2.000000000000000000000000000000, which does not account for the possibility of a 1-ULP (Unit in the Last Place) error. The binary representation of 2.0 as a double-precision float is exact, but the computed result via log()/log(10) may differ by a single ULP due to rounding, leading to a decimal representation that appears incorrect.

Resolving Floating-Point Discrepancies in log10() via Code Correction and Test Adjustment

Step 1: Reproduce the Issue
Run the failing test in isolation using SQLite’s test harness:

./testfixture test.db -v func7-pg-181

Confirm the output matches 1.999999999999999000000000000000 on 32-bit x86 systems. On 64-bit systems, the same test should return 2.000000000000000000000000000000.

Step 2: Inspect SQLite’s log10() Implementation
Examine the SQLite source code to verify whether log10() is implemented via the log() function. In versions ≤3.40.0, the code in func.c uses:

sqlite3_result_double(context, log(x)/log(10.0));

This method is prone to precision loss, especially when log(10.0) is not represented exactly.

Step 3: Update to SQLite ≥3.41.0
The fix (commit 7c572d02e60a83b3) replaces the formula-based log10() with a lookup table for common values and direct computation using high-precision algorithms for others. Apply this patch or upgrade to SQLite 3.41.0+.

Step 4: Validate the Fix
Rebuild SQLite and rerun the test:

./configure --enable-math && make && ./testfixture test.db -v func7-pg-181

The output should now match 2.000000000000000000000000000000 on all architectures.

Step 5: Adjust Tests for Floating-Point Tolerance (If Necessary)
For environments where upgrading SQLite is not immediately feasible, modify the test to accept a range of values. For example:

SELECT abs(format('%.30f', log10(100.0)) - 2.0) < 1e-15;

This allows a tolerance of 1e-15, accommodating ULP errors without compromising test validity.

Step 6: Analyze Cross-Platform Floating-Point Consistency
Investigate the use of compiler flags (-msse2 -mfpmath=sse on 32-bit x86) to enforce consistent floating-point behavior across architectures. This forces the use of SSE instructions instead of x87 FPU, aligning 32-bit computation with 64-bit systems.

Step 7: Review Platform-Specific libm Implementations
Differences in libm (e.g., glibc vs. musl) can affect the accuracy of log(). Test SQLite against multiple C libraries to identify implementation-specific anomalies.

Step 8: Document Platform-Specific Edge Cases
Update internal documentation to highlight floating-point function behavior on 32-bit systems, ensuring developers account for ULP errors in high-precision formatting scenarios.

Final Resolution: The definitive solution is upgrading to SQLite ≥3.41.0, which includes the corrected log10() implementation. For legacy systems, combining test adjustments with compiler flags mitigates the issue without code changes.