Rounding Errors in SQLite format() with %f/%g and Precision 0

Issue Overview: format() Function Rounding Inconsistencies in Recent SQLite Versions

The SQLite format() function, which leverages the printf()-style formatting engine, has exhibited unexpected rounding behavior when using %f or %g conversion specifiers with a precision of 0 in versions 3.43.0 and later. This regression introduces discrepancies compared to prior versions (e.g., 3.42.0) and deviates from standard C printf() behavior. Key examples include:

• Case 1: %0.0f Conversion
  In SQLite 3.42.0, SELECT format('%0.0f', 0.9); returns 1, adhering to correct rounding rules (rounding 0.9 to the nearest integer). However, versions 3.43.0 through 3.45.1 return 0, truncating the value instead of rounding.
  Expected Behavior: 0.9 → 1 (rounded up).
  Observed Behavior: 0.9 → 0 (truncated).

• Case 2: %0.0g Conversion with Sub-1 Values
  For SELECT format('%0.0g', 0.09);, SQLite 3.42.0 returns 0.09, preserving the value as-is with a precision of 0 for %g (interpreted as 1 significant digit). In 3.45.1, this becomes 0.08, incorrectly rounding down.
  Expected Behavior: 0.09 → 0.1 (rounded to 1 significant digit).
  Observed Behavior: 0.09 → 0.08 (incorrect rounding).

• Case 3: %0.0g Conversion with Values ≥1
  SELECT format('%0.0g', 1.9); returns 2 in SQLite 3.42.0 (correct rounding to 1 significant digit) but 1 in 3.45.1 (truncation).
  Expected Behavior: 1.9 → 2 (rounded up).
  Observed Behavior: 1.9 → 1 (truncated).

This regression stems from changes in SQLite’s internal rounding logic for floating-point conversions. The issue is compounded by ambiguities in SQLite’s documentation regarding the interpretation of precision for %g/%G conversions. Specifically, the documentation incorrectly states that precision for %g/%G specifies the number of digits after the decimal point, whereas it should define the number of significant digits, aligning with C standard printf() behavior.

Possible Causes: Precision Handling and Rounding Logic Changes

1. Incorrect Rounding Logic for Zero-Precision Conversions

In SQLite versions 3.43.0–3.45.1, the internal implementation of %0.0f and %0.0g conversions erroneously truncated fractional values instead of applying proper rounding. For %f, precision 0 implies rounding to the nearest integer. For %g, precision 0 is equivalent to precision 1 (1 significant digit) per C standard conventions. The faulty logic caused:

• 0.9 to be truncated to 0 instead of rounded to 1.
• 1.9 to be truncated to 1 instead of rounded to 2.
• Sub-1 values like 0.09 to be mishandled due to incorrect exponent adjustments during rounding.

2. Documentation Misalignment with C Standard Behavior

SQLite’s printf() documentation inaccurately states:

"For floating-point substitutions (%e, %E, %f, %g, %G) the precision specifies the number of digits to display to the right of the decimal point."

This is incorrect for %g/%G, where precision defines the number of significant digits, not decimal places. For example:

• %0.0g on 0.09 should round to 0.1 (1 significant digit), not 0.09 (2 decimal places).
• %0.0g on 1.9 should round to 2 (1 significant digit), not 1.9 (1 decimal place).

The documentation error creates confusion, as users expect SQLite’s format() to behave like C’s printf(), which defines precision for %g as significant digits.

3. Ambiguity in Handling Zero Precision for %g/%G

The C standard (C11 §7.21.6.1) specifies that a precision of 0 for %g/%G is treated as 1. SQLite’s implementation in affected versions mishandled edge cases where rounding a value to 1 significant digit required adjusting the exponent (e.g., 0.09 → 0.1), leading to incorrect results like 0.08.

Troubleshooting Steps, Solutions & Fixes

1. Verify SQLite Version and Apply Patches

Step 1: Check SQLite Version
Run SELECT sqlite_version(); to confirm the version. Affected versions are 3.43.0–3.45.1.

Step 2: Update to SQLite 3.45.2 or Later
The fix was implemented in check-in 7fca1bc482fc2456. Ensure your SQLite build includes this commit or use version 3.45.2+.

Step 3: Validate Rounding Behavior
Re-run problematic queries:

SELECT format('%0.0f', 0.9);  -- Should return 1
SELECT format('%0.0g', 0.09); -- Should return 0.1
SELECT format('%0.0g', 1.9);  -- Should return 2

If results match expectations, the fix is effective.

2. Workarounds for Unpatched Versions

If updating is not feasible, use explicit rounding functions:

-- For %0.0f behavior:
SELECT CAST(ROUND(0.9) AS INTEGER); -- Returns 1

-- For %0.0g behavior:
SELECT 
  CASE 
    WHEN ABS(value) < 1 THEN ROUND(value * 10) / 10
    ELSE ROUND(value)
  END;

This mimics %g’s significant digit rounding by scaling sub-1 values before rounding.

3. Correct Documentation Interpretation

Revise understanding of %g/%G precision:

• Precision = Significant Digits, not decimal places.
• Precision 0 = Precision 1 for %g/%G.

Example Corrections:

• %0.0g on 0.09 → 0.1 (1 significant digit).
• %0.0g on 1234 → 1e+03 (1 significant digit in exponential notation).

4. Adjust Application Logic for Precision Handling

When migrating from SQLite <3.43.0 to ≥3.43.0, audit queries using format() with %f/%g and precision 0. Test edge cases like:

• Values ending in .5 (rounding midpoint).
• Sub-1 values requiring exponent adjustments.

5. Contribute to Documentation Clarification

The SQLite documentation will be updated to reflect:

For floating-point substitutions (%e, %E, %f) the precision specifies the number of digits to display to the right of the decimal point. For %g and %G, the precision specifies the number of significant digits. A precision of 0 for %g or %G is treated as 1.

Monitor SQLite’s printf() documentation for updates and adjust coding practices accordingly.

By following these steps, users can resolve rounding inconsistencies, align with C standard behavior, and ensure accurate results in format()-driven queries.