I understand that SQLite can support REAL floating-point values; the values are stored in 8-byte C double type, and may be output using printf in standard C-printf floating-point formats. Also SQLite supports a non-standard printf format for "extended precision" aka "alternate-form-2" flag, that is outputting up to 26 significant digits.

I wonder, if generally the double type allows 15 to 18 significant digits, what's the meaningfulness of the "extended" 11 to 8 digits? Do these directly relate to double representation or come from long double as used internally?

P.S. By the way, apparently there are some typos in the doc description for "!" option flag field ("alternate-form-2" flag):

• an missing closing quote in "alternate-form-2" flag
• "understood" instead of "understand"
• missing quotes in the second mention of the "alternate-form-2"

Corrections applied:

This is the "alternate-form-2" flag. For string substitutions, this flag causes the width and precision to be understood in terms of characters rather than bytes. For floating point substitutions, the "alternate-form-2" flag increases the maximum number of significant digits displayed from 16 to 26, forces the display of the decimal point and causes at least one digit to appear after the decimal point.

Well, you already read most of the sources I would have pointed to.

SQLite stores real numbers in big-endian IEEE 754-2008 64-bit floating point. Handling of these numbers matches many of the standard libraries which deal with them. Anything you can look up about precision, accuracy, equality tests, or anything else about that format applies to SQLite's handling of them. If you're obtaining a real value using a C library, you should consider that the value was converted from IEEE to whatever internal format your implementation of C uses.

From the printf page you pointed to …

Every IEEE-754 double can be represented exactly as a decimal floating-point value, but some doubles require more than 16 or 26 significant digits.

The reverse is not true. Some numbers will change in the conversion from a text representation to IEEE format. Beware.

Here are some examples to demonstrate the question.

Let's take a value 1.2345678901234567e+04 (17 significant digits) and get its decimal representation in:

• C 8-byte double floating-point value (formatted as "%.25e" with gcc printf),
• C long double floating-point value (formatted as "%.25Le" with gcc printf),
• as displayed by SQLite printf with "alternate-form-2" format "%!.25e":

SQLite version 3.41.0 2023-02-21 18:09:37

sqlite> select printf('%!.25e', 1.2345678901234567e+04);
1.2345678901234567092972532e+04

Here are all of the representations together:

     value: 1.2345678901234567000000000e+04
 C "%.25e": 1.2345678901234567092615180e+04   (double)
  "%!.25e": 1.2345678901234567092972532e+04   (SQLite)
C "%.25Le": 1.2345678901234567000244624e+04   (long double)

As one can see, all of the representations retain at least 16 significant figures, as expected in this case for a double.

However, looking beyond the 16 figures, there is some divergence in the displayed values. I understand that these "extra" digits are not random and are due to the IEEE 754 floating-point resolution, in other words, that's how the real value maps onto available floating-point value. long double offers a higher resolution, so to speak and is the closest to the real value.

SQLite, as I understand, supports only the double. Yet the SQLite's printed value shows some other version of the "extended" digits. Thus my question about the meaningfulness of these digits. They don't match either double or long double, though closer to double in this case. Does this extended format mean to match the 'double' representation?

This can be observed also with other sample values.

I see the same except:

sqlite> select format('%!.26e', 1.2345678901234567e4);
┌────────────────────────────────────────┐
│ format('%!.26e', 1.2345678901234567e4) │
├────────────────────────────────────────┤
│ '1.2345678901234567092615179e+04'      │
└────────────────────────────────────────┘

because I have declared LONGDOUBLE_TYPE as __float128 in order to maintain precision when the DLL is loaded on Windows.

The version compiled with Microsoft Compilers (which do not use extended precision/long double) is, of course, the very innaccurate:

sqlite> select format('%!.26e', 1.2345678901234567e4);
┌────────────────────────────────────────┐
│ format('%!.26e', 1.2345678901234567e4) │
├────────────────────────────────────────┤
│ '1.2345678901234566904321354e+04'      │
└────────────────────────────────────────┘

Half-a-Century of numerical analysis and precision destroyed by Microsoft in but the flash of an eye.

You can "run the conversion" process to any number of output digits your little heart desires. SQLite3, and various other implementations for converting IEEE-754 floating point values to text, have chosen "arbitrary limits" for how long to "loop around the algorithm" which produces output digits.

Whether these "output digits" have any meaning whatsoever is to be determined by the user of the output. Just because you ask for 5.2f output formatting does not mean that the value is between 99.99 and -9.99 and that the rest of the significant digits are zero. Nor does requesting the format .26e mean that the value contains 26 significant digits.

If other words, just because you asked the question:

for i in range(4000):
    digit = int(value)
    print(digit,end='')
    value = (value - digit)*10
print()

does not mean that the 4000 digits thus printed have any meaning whatsoever.

An IEEE-754 binary64 floating point number contains about 15.95 decimal digits of accuracy. This means that any 17 digit represenation should be sufficient to express the value approximated and to "trip it" though properly functioning input/output routines.

CORRECTION Experiments reveal that 17 digits ('%!.17g') is insufficient to accurately represent binary64 values so that the following evaluates to True for a random sampling of representable binary64 numbers:

     value == cast(format('%!.*g', digits, value) as real)
or
     value == cast(format('%!.*e', digits, value) as real)

Testing reveals,

Testing select cast(format('%!.*g', ?, ?) as real)
Samples=1000000; digits=15; Success=8.7744%, Elapsed=32.74039959907532
Samples=1000000; digits=16; Success=73.1633%, Elapsed=32.57332420349121
Samples=1000000; digits=17; Success=99.9703%, Elapsed=32.556875705718994
Samples=1000000; digits=18; Success=100.0%, Elapsed=32.38529634475708
Samples=1000000; digits=19; Success=100.0%, Elapsed=32.667312145233154
Samples=1000000; digits=20; Success=100.0%, Elapsed=32.52582859992981

Testing select cast(format('%!.*e', ?, ?) as real)
Samples=1000000; digits=15; Success=73.1151%, Elapsed=33.04781651496887
Samples=1000000; digits=16; Success=99.9752%, Elapsed=32.81526207923889
Samples=1000000; digits=17; Success=100.0%, Elapsed=33.11777186393738
Samples=1000000; digits=18; Success=100.0%, Elapsed=33.17774248123169
Samples=1000000; digits=19; Success=100.0%, Elapsed=33.595762729644775
Samples=1000000; digits=20; Success=100.0%, Elapsed=33.415281772613525

So "some" values can be expressed in 15 digits, "most" in 17 digits, and "all that were tested" in 18 digits. That does not mean that there are not values that cannot be encoded in 18 digits, merely that we didn't stumble on one of them. Since quote() will use 20 digits if 15 are insufficient, it should cover the entire range of valid binary64 values.

So, while printing more than 18 significant digits may not portray any additional accuracy, it cannot hurt. As always, interpretation of the result (that is, the printed number) is an exercize for the reader of the number.

I'm curious about the cases where 17 digits is insufficient. That is... unexpected to me.

I should think so too, but it is a rounding error. I modified the python searcher as follows:

import defaults

from math import ulps
from mpsw import Connection
from random import random, randint
from sys import argv
from time import time

if len(argv) > 1:
    numtest = int(argv[1])
else:
    numtest = 1000

if len(argv) > 2:
    type = argv[2]
else:
    type = 'g'

sql = f"select cast(format('%!.*{type}', ?, ?) as real)"
sql2 = f"select quote(value), quote(newvalue), ulps(value, newvalue) as distance from (select value, cast(format('%!.*{type}', ?, value) as real) as newvalue from (select ? as value))"

db = Connection()

print(f'Testing {sql}')
for digits in range(15,26):
    start = time()
    success = 0
    failure = 0
    for i in range(numtest):
        x = random() * 10 ** randint(-250,250)
        y = db.executex(sql, digits, x).scalar
        if (x == y):
            success += 1
        else:
            failure += 1
            if failure == 1:
                print()
            print(f"Failed={i}; format='%!.{digits}{type}'; value={x:.20{type}}; newvalue={y:.20{type}};")
            print(db.executex(sql2, digits, x).getone)
            if failure > 4:
                break
    print(f'digits={digits}; Success={success*100.0/numtest}%, Elapsed={time()-start}')
    if success == numtest:
        break

Note that the ulps function is nonstandard. It computes the distance in units of an ulp of the first argument to get to the second argument from the first.
db.executex(sql, arg1, arg2).scalar -> db.execute(sql, (arg1, arg2)).fetchone()[0]
db.executex(sql, arg1, arg2).getone -> db.execute(sql, (arg1, arg2)).fetchone()

gives:

>pylength 1000000
Testing select cast(format('%!.*g', ?, ?) as real)

Failed=0; format='%!.15g'; value=5.2221782415954320706e+205; newvalue=5.2221782415954302884e+205;
Row(quote_value='5.2221782415954319e+205', quote_newvalue='5.2221782415954301e+205', distance=2.0)
Failed=2; format='%!.15g'; value=5.7736128867270089799e-112; newvalue=5.7736128867270099032e-112;
Row(quote_value='5.773612886727009e-112', quote_newvalue='5.7736128867270099e-112', distance=-1.0)
Failed=3; format='%!.15g'; value=2.94579182285687791e-189; newvalue=2.9457918228568799034e-189;
Row(quote_value='2.9457918228568779e-189', quote_newvalue='2.9457918228568799e-189', distance=-5.0)
Failed=4; format='%!.15g'; value=4.5676730462017810144e-244; newvalue=4.5676730462017797136e-244;
Row(quote_value='4.567673046201781e-244', quote_newvalue='4.5676730462017797e-244', distance=2.0)
Failed=5; format='%!.15g'; value=4.2318140566739305112e-17; newvalue=4.2318140566739298949e-17;
Row(quote_value='4.2318140566739305e-17', quote_newvalue='4.2318140566739299e-17', distance=1.0)
digits=15; Success=0.0001%, Elapsed=0.001983642578125

Failed=14; format='%!.16g'; value=1.8291647503245967142e+174; newvalue=1.8291647503245969339e+174;
Row(quote_value='1.8291647503245967e+174', quote_newvalue='1.8291647503245969e+174', distance=-1.0)
Failed=15; format='%!.16g'; value=3.8322298685868813223e-66; newvalue=3.8322298685868807952e-66;
Row(quote_value='3.8322298685868813e-66', quote_newvalue='3.8322298685868808e-66', distance=1.0)
Failed=27; format='%!.16g'; value=2.2068245097934403056e+65; newvalue=2.206824509793439838e+65;
Row(quote_value='2.2068245097934403e+65', quote_newvalue='2.2068245097934398e+65', distance=1.0)
Failed=29; format='%!.16g'; value=4.4177277958429775693e+55; newvalue=4.4177277958429781137e+55;
Row(quote_value='4.4177277958429776e+55', quote_newvalue='4.4177277958429781e+55', distance=-1.0)
Failed=31; format='%!.16g'; value=1.3988746730218004424e+138; newvalue=1.3988746730217999466e+138;
Row(quote_value='1.3988746730218004e+138', quote_newvalue='1.3988746730217999e+138', distance=3.0)
digits=16; Success=0.0027%, Elapsed=0.002499818801879883

Failed=2921; format='%!.17g'; value=1.2898524805315169909e+203; newvalue=1.2898524805315168169e+203;
Row(quote_value='1.28985248053151694993e+203', quote_newvalue='1.2898524805315168e+203', distance=1.0)
Failed=2997; format='%!.17g'; value=1.2203276673459828799e+238; newvalue=1.2203276673459827353e+238;
Row(quote_value='1.22032766734598284105e+238', quote_newvalue='1.2203276673459827e+238', distance=1.0)
Failed=5706; format='%!.17g'; value=1.0813550607292020836e+213; newvalue=1.081355060729201934e+213;
Row(quote_value='1.08135506072920204909e+213', quote_newvalue='1.0813550607292019e+213', distance=1.0)
Failed=5906; format='%!.17g'; value=1.0538191214111336786e+235; newvalue=1.0538191214111335374e+235;
Row(quote_value='1.05381912141113364517e+235', quote_newvalue='1.0538191214111335e+235', distance=1.0)
Failed=10936; format='%!.17g'; value=1.099069140816824076e+244; newvalue=1.0990691408168239244e+244;
Row(quote_value='1.099069140816824041e+244', quote_newvalue='1.0990691408168239e+244', distance=1.0)
digits=17; Success=1.0932%, Elapsed=0.3460075855255127
digits=18; Success=100.0%, Elapsed=31.507839679718018

You can also run this long CTE / statement which will generate the various results for you using native sqlite3. Note that I have added some UDFs that I use here:

drop table if exists temp.trippable;
create virtual table temp.trippable using statement((
with
     tests(tests, startat) as materialized
     (
         values (cast(coalesce(:tests, 1e999) as integer), unixepoch())
     ),
     digits(digits, startat) as materialized
     (
         values (cast(coalesce(:digits, 18) as integer), unixepoch())
     ),
     randomreal(value) as not materialized
     (
         select randomv() * pow(10, 308 - randomv(616))
      union all
         select case when randomV(3) < 1
                     then trunc(randomv() * pow(2, randomv(63)))
                     else randomv() * pow(10, 308 - randomv(616))
                 end
           from randomreal
          limit (select tests from tests limit 1)
     ),
     step1(row, value, newvalue) as not materialized
     (
         select row_number() over (rows between unbounded preceding and current row),
                value,
                cast(format('%!.*g', (select digits from digits limit 1), value) as real)
           from randomreal
     ),
     step2(unixstamp, row, ST, SigDigits, BadCount, ExactValue, ExactNewValue, Distance) as not materialized
     (
         select unixstamp(),
                row,
                iif(value == newvalue, 'OK', '**'),
                (select digits from digits limit 1),
                coalesce(sum(1) filter (where value != newvalue) over (rows between unbounded preceding and current row), 0),
                format('%!.20e', value),
                format('%!.20e', newvalue),
                ulps(value, newvalue)
           from step1
          where value != newvalue
             or row % 1000000 == 0
     ),
     results(unixstamp, prevstamp, row, ST, SigDigits, BadCount, ExactValue, ExactNewValue, Distance) as not materialized
     (
         select unixstamp,
                lag(unixstamp) over (rows between unbounded preceding and current row),
                row,
                ST,
                SigDigits,
                BadCount,
                ExactValue,
                ExactNewValue,
                Distance
           from step2
     )
select elapsedTime(unixstamp - unixepoch()) as ElapsedTime,
       elapsedTime(unixstamp - coalesce(prevstamp, unixepoch())) as StepTime,
       row,
       ST,
       SigDigits,
       BadCount,
       ExactValue,
       ExactNewValue,
       Distance,
       cast(row / (unixstamp - unixepoch()) as integer) as RPS
  from results
));

which basically generates a buch of random real values of various random significand length and exponent, and then "round trips" them though real -> text -> real and reports on the differences found between the pre-trip number and the post-trip number.

I get the following which is cancelled because there are a lot of errors.

SQLite version 3.42.0 2023-03-02 03:40:38
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .read trippable3.sql
VM-steps: 5
Run Time: real 0.001 user 0.000000 sys 0.000000
VM-steps: 38
Run Time: real 0.002 user 0.000000 sys 0.000000
sqlite> .mode list
sqlite> select * from trippable where digits=18;
ElapsedTime|StepTime|row|ST|SigDigits|BadCount|ExactValue|ExactNewValue|Distance|RPS
0:00:00:03.314|0:00:00:03.314|1000000|OK|18|0|7.26288481980216768258e-13|7.26288481980216768258e-13|0.0|301750
0:00:00:05.076|0:00:00:01.762|2000000|OK|18|0|9.13500081005519915661e+247|9.13500081005519915661e+247|0.0|394011
0:00:00:06.781|0:00:00:01.705|3000000|OK|18|0|3.561011e+06|3.561011e+06|0.0|442412
0:00:00:08.543|0:00:00:01.762|4000000|OK|18|0|5.66720275328362484926e-308|5.66720275328362484926e-308|0.0|468219
0:00:00:10.299|0:00:00:01.756|5000000|OK|18|0|4.080204995695e+12|4.080204995695e+12|0.0|485484
0:00:00:11.984|0:00:00:01.685|6000000|OK|18|0|2.90652702648846199984e+16|2.90652702648846199984e+16|0.0|500667
0:00:00:13.741|0:00:00:01.757|7000000|OK|18|0|2.52e+03|2.52e+03|0.0|509424
0:00:00:15.519|0:00:00:01.778|8000000|OK|18|0|8.78535341428563051457e+202|8.78535341428563051457e+202|0.0|515497
0:00:00:17.177|0:00:00:01.658|9000000|OK|18|0|9.75503344160813248408e+46|9.75503344160813248408e+46|0.0|523956
0:00:00:18.867|0:00:00:01.690|10000000|OK|18|0|7.71000977033070721737e+175|7.71000977033070721737e+175|0.0|530025
0:00:00:20.579|0:00:00:01.712|11000000|OK|18|0|6.9298e+05|6.9298e+05|0.0|534525
0:00:00:22.301|0:00:00:01.722|12000000|OK|18|0|9.9953556572004048494e+80|9.9953556572004048494e+80|0.0|538092
0:00:00:23.997|0:00:00:01.696|13000000|OK|18|0|1.0157240753e+10|1.0157240753e+10|0.0|541734
0:00:00:25.721|0:00:00:01.724|14000000|OK|18|0|7.81776283573323898298e-73|7.81776283573323898298e-73|0.0|544302
0:00:00:27.463|0:00:00:01.742|15000000|OK|18|0|7.38045526054051092505e-60|7.38045526054051092505e-60|0.0|546189
0:00:00:29.129|0:00:00:01.666|16000000|OK|18|0|3.6110477391338e+13|3.6110477391338e+13|0.0|549280
0:00:00:30.783|0:00:00:01.654|17000000|OK|18|0|7.48293182890370727443e-305|7.48293182890370727443e-305|0.0|552252
VM-steps: 247
Run Time: real 32.553 user 32.468750 sys 0.015625
Runtime error: interrupted (9)
sqlite> select * from trippable where digits=17;
ElapsedTime|StepTime|row|ST|SigDigits|BadCount|ExactValue|ExactNewValue|Distance|RPS
0:00:00:00.009|0:00:00:00.009|2324|**|17|1|1.36256838446203883119e+302|1.36256838446203864081e+302|1.0|258227
0:00:00:00.009|0:00:00:00.000|4712|**|17|2|1.13594395181982103998e+251|1.13594395181982091281e+251|1.0|523565
0:00:00:00.015|0:00:00:00.006|4991|**|17|3|2.1395566994294184372e+304|2.13955669942941819347e+304|1.0|332736
0:00:00:00.020|0:00:00:00.005|6658|**|17|4|1.36820664295370094418e+256|1.36820664295370077742e+256|1.0|332900
0:00:00:00.030|0:00:00:00.010|8645|**|17|5|1.35712066270773704026e+225|1.35712066270773687589e+225|1.0|288166
0:00:00:00.036|0:00:00:00.006|12953|**|17|6|1.12218534976779884876e+287|1.12218534976779867962e+287|1.0|359807
0:00:00:00.038|0:00:00:00.002|15182|**|17|7|1.00123793803057844532e+238|1.00123793803057830069e+238|1.0|399527
0:00:00:00.041|0:00:00:00.003|15799|**|17|8|1.04614369615339010996e+301|1.04614369615338999091e+301|1.0|385342
0:00:00:00.044|0:00:00:00.003|16625|**|17|9|1.24381792439419473664e+235|1.24381792439419459547e+235|1.0|377841
0:00:00:00.046|0:00:00:00.002|18145|**|17|10|1.35368919772971671401e+303|1.35368919772971656179e+303|1.0|394456
0:00:00:00.048|0:00:00:00.002|18384|**|17|11|2.56248389625092064659e+306|2.56248389625092033478e+306|1.0|383001
0:00:00:00.050|0:00:00:00.002|18701|**|17|12|1.1675274345261297303e+306|1.16752743452612957439e+306|1.0|374020
0:00:00:00.052|0:00:00:00.002|18863|**|17|13|1.02903379387881283686e+300|1.02903379387881268821e+300|1.0|362751
0:00:00:00.055|0:00:00:00.003|19029|**|17|14|1.21135673063968004865e+225|1.21135673063967988429e+225|1.0|345982
0:00:00:00.066|0:00:00:00.011|20300|**|17|15|5.21339726294462655004e+300|5.21339726294462595503e+300|1.0|307575
0:00:00:00.075|0:00:00:00.009|26089|**|17|16|1.01026034038592753554e+245|1.01026034038592741432e+245|1.0|347854
0:00:00:00.079|0:00:00:00.004|30022|**|17|17|3.34799820452375303464e+302|3.34799820452375265387e+302|1.0|380025
0:00:00:00.081|0:00:00:00.002|30974|**|17|18|1.14593592234567864882e+219|1.14593592234567849205e+219|1.0|382395
0:00:00:00.083|0:00:00:00.002|31535|**|17|19|1.40496674372301383858e+219|1.4049667437230136818e+219|1.0|379940
0:00:00:00.086|0:00:00:00.003|31741|**|17|20|1.00181248286110033374e+211|1.00181248286110021698e+211|1.0|369081
0:00:00:00.096|0:00:00:00.010|32775|**|17|21|1.20363089314387504034e+219|1.20363089314387488356e+219|1.0|341406
0:00:00:00.099|0:00:00:00.003|37667|**|17|22|1.01303969646216984106e+285|1.01303969646216970889e+285|1.0|380474
0:00:00:00.102|0:00:00:00.003|37800|**|17|23|1.02976458486298144451e+213|1.029764584862981295e+213|1.0|370588
0:00:00:00.105|0:00:00:00.003|38874|**|17|24|1.28342420229700532135e+303|1.28342420229700516913e+303|1.0|370229
0:00:00:00.111|0:00:00:00.006|40289|**|17|25|1.39496020326487644015e+284|1.39496020326487627503e+284|1.0|362964
0:00:00:00.120|0:00:00:00.009|42884|**|17|26|1.08482996122194553416e+217|1.08482996122194541176e+217|1.0|357367
0:00:00:00.123|0:00:00:00.003|46500|**|17|27|1.00356383652730694758e+220|1.00356383652730682214e+220|1.0|378049
0:00:00:00.131|0:00:00:00.008|47349|**|17|28|1.19840677438015924051e+219|1.19840677438015908372e+219|1.0|361443
0:00:00:00.134|0:00:00:00.003|51461|**|17|29|2.4839303232459292402e+306|2.48393032324592892838e+306|1.0|384037
0:00:00:00.137|0:00:00:00.003|51919|**|17|30|1.07729599340120564771e+158|1.07729599340120552573e+158|1.0|378971
0:00:00:00.139|0:00:00:00.002|52914|**|17|31|1.15576644471842164302e+305|1.15576644471842144819e+305|1.0|380676
0:00:00:00.154|0:00:00:00.015|53547|**|17|32|1.10914364161942353775e+254|1.10914364161942340753e+254|1.0|347708
0:00:00:00.160|0:00:00:00.006|62058|**|17|33|1.89699561717049914452e+301|1.89699561717049890651e+301|1.0|387862
0:00:00:00.165|0:00:00:00.005|64236|**|17|34|1.11079310993865314845e+305|1.11079310993865295362e+305|1.0|389309
0:00:00:00.168|0:00:00:00.003|66094|**|17|35|1.49390360146327094911e+274|1.49390360146327075686e+274|1.0|393416
0:00:00:00.177|0:00:00:00.009|66294|**|17|36|4.45928053482063674502e+307|4.45928053482063624585e+307|1.0|374542
0:00:00:00.182|0:00:00:00.005|71361|**|17|37|1.43259547004518494701e+265|1.43259547004518476799e+265|1.0|392093
0:00:00:00.185|0:00:00:00.003|72234|**|17|38|1.13302575610628584838e+232|1.13302575610628571047e+232|1.0|390454
0:00:00:00.190|0:00:00:00.005|73317|**|17|39|4.16386188811922203917e+301|4.16386188811922156342e+301|1.0|385879
0:00:00:00.194|0:00:00:00.004|75473|**|17|40|1.08578471050647493655e+291|1.08578471050647479809e+291|1.0|389036
0:00:00:00.202|0:00:00:00.008|77023|**|17|41|1.18839393484856924165e+288|1.18839393484856910634e+288|1.0|381302
0:00:00:00.209|0:00:00:00.007|79513|**|17|42|4.33525106534717294577e+304|4.33525106534717245875e+304|1.0|380445
0:00:00:00.212|0:00:00:00.003|81971|**|17|43|1.25173061727175414549e+228|1.25173061727175397722e+228|1.0|386655
0:00:00:00.216|0:00:00:00.004|82232|**|17|44|1.07893975820907204944e+130|1.07893975820907192639e+130|1.0|380703
0:00:00:00.223|0:00:00:00.007|83249|**|17|45|1.29405147617420284637e+290|1.29405147617420267333e+290|1.0|373314
0:00:00:00.227|0:00:00:00.004|86439|**|17|46|1.28251259141923714714e+216|1.28251259141923699405e+216|1.0|380788
0:00:00:00.229|0:00:00:00.002|87055|**|17|47|1.48912621112252364493e+209|1.48912621112252346248e+209|1.0|380153
0:00:00:00.231|0:00:00:00.002|87595|**|17|48|2.00335049822589123815e+301|2.00335049822589100028e+301|1.0|379199
0:00:00:00.235|0:00:00:00.004|87923|**|17|49|1.56625075141872644617e+299|1.56625075141872626034e+299|1.0|374140
0:00:00:00.240|0:00:00:00.005|89423|**|17|50|1.27846854460868123695e+238|1.27846854460868109243e+238|1.0|372595
0:00:00:00.243|0:00:00:00.003|90638|**|17|51|1.06448839642303003355e+248|1.0644883964230299093e+248|1.0|372996
0:00:00:00.247|0:00:00:00.004|91225|**|17|52|1.03460547245882913448e+276|1.03460547245882901153e+276|1.0|369332
0:00:00:00.250|0:00:00:00.003|93136|**|17|53|1.30684777968224184838e+275|1.30684777968224169453e+275|1.0|372544
0:00:00:00.253|0:00:00:00.003|94011|**|17|54|1.82541007825305344816e+286|1.82541007825305323687e+286|1.0|371585
0:00:00:00.255|0:00:00:00.002|94286|**|17|55|1.08179846074339334062e+254|1.0817984607433932104e+254|1.0|369749
0:00:00:00.257|0:00:00:00.002|94524|**|17|56|1.27388009956326143999e+294|1.27388009956326129818e+294|1.0|367797
0:00:00:00.260|0:00:00:00.003|94790|**|17|57|1.69981269191361424554e+277|1.69981269191361404873e+277|1.0|364576
0:00:00:00.277|0:00:00:00.017|96080|**|17|58|2.06132391910434584897e+304|2.06132391910434560546e+304|1.0|346859
0:00:00:00.282|0:00:00:00.005|105232|**|17|59|4.13054538353710244897e+304|4.13054538353710196194e+304|1.0|373163
0:00:00:00.285|0:00:00:00.003|106699|**|17|60|1.61423229823654362335e+305|1.61423229823654342841e+305|1.0|374382
0:00:00:00.287|0:00:00:00.002|107482|**|17|61|1.09045091738544624396e+235|1.09045091738544610269e+235|1.0|374501
0:00:00:00.289|0:00:00:00.002|108389|**|17|62|1.10703976417575913101e+303|1.10703976417575897879e+303|1.0|375048
0:00:00:00.292|0:00:00:00.003|108727|**|17|63|1.49192775532934454292e+259|1.49192775532934437227e+259|1.0|372352
0:00:00:00.293|0:00:00:00.001|109039|**|17|64|1.37287341318712714929e+256|1.37287341318712698254e+256|1.0|372146
0:00:00:00.294|0:00:00:00.001|109065|**|17|65|1.22486963177335413371e+294|1.22486963177335399189e+294|1.0|370969
0:00:00:00.300|0:00:00:00.006|109417|**|17|66|1.06246824479331382947e+303|1.06246824479331367725e+303|1.0|364723
0:00:00:00.311|0:00:00:00.011|111912|**|17|67|1.22977222430471823887e+266|1.22977222430471809565e+266|1.0|359845
0:00:00:00.318|0:00:00:00.007|117377|**|17|68|1.49520979899248984005e+302|1.49520979899248964979e+302|1.0|369110
0:00:00:00.321|0:00:00:00.003|119541|**|17|69|1.43909264099791783754e+281|1.43909264099791767629e+281|1.0|372402
0:00:00:00.325|0:00:00:00.004|120002|**|17|70|3.12296123429472154598e+302|3.1229612342947211652e+302|1.0|369237
0:00:00:00.332|0:00:00:00.007|121858|**|17|71|1.05837813267774722119e+307|1.0583781326777470964e+307|1.0|367042
0:00:00:00.334|0:00:00:00.002|124835|**|17|72|1.70634100983483734999e+305|1.70634100983483715506e+305|1.0|373757
0:00:00:00.338|0:00:00:00.004|125068|**|17|73|1.18885330314729924509e+278|1.18885330314729908767e+278|1.0|370023
0:00:00:00.344|0:00:00:00.006|126625|**|17|74|1.14920900945735744594e+225|1.14920900945735728157e+225|1.0|368096
0:00:00:00.348|0:00:00:00.004|129259|**|17|75|1.21581006814834303899e+235|1.21581006814834289783e+235|1.0|371434
0:00:00:00.350|0:00:00:00.002|129763|**|17|76|1.18508094690248894282e+269|1.18508094690248879624e+269|1.0|370751
0:00:00:00.355|0:00:00:00.005|130235|**|17|77|1.03461640716972834636e+219|1.03461640716972818959e+219|1.0|366859
0:00:00:00.361|0:00:00:00.006|132297|**|17|78|1.26842008161026564274e+219|1.26842008161026548597e+219|1.0|366473
0:00:00:00.369|0:00:00:00.008|133291|**|17|79|1.35864023269491824273e+216|1.35864023269491808964e+216|1.0|361222
0:00:00:00.386|0:00:00:00.017|133915|**|17|80|1.04759403429430733285e+282|1.04759403429430720383e+282|1.0|346930
0:00:00:00.390|0:00:00:00.004|137295|**|17|81|1.35970028159740434058e+302|1.3597002815974041502e+302|1.0|352038
0:00:00:00.398|0:00:00:00.008|138290|**|17|82|2.64864832863306552592e+303|2.64864832863306522148e+303|1.0|347462
0:00:00:00.402|0:00:00:00.004|141991|**|17|83|1.13122488367804204076e+251|1.13122488367804191358e+251|1.0|353211
0:00:00:00.408|0:00:00:00.006|142995|**|17|84|2.06543947479394644274e+301|2.06543947479394620486e+301|1.0|350477
0:00:00:00.414|0:00:00:00.006|146278|**|17|85|1.14330796637229954086e+244|1.14330796637229938918e+244|1.0|353328
0:00:00:00.418|0:00:00:00.004|148180|**|17|86|2.70849561206117444124e+303|2.70849561206117413658e+303|1.0|354497
0:00:00:00.425|0:00:00:00.007|148980|**|17|87|2.17391860444079643242e+307|2.17391860444079618283e+307|1.0|350541
0:00:00:00.431|0:00:00:00.006|152222|**|17|88|1.76010999322252764381e+277|1.76010999322252744692e+277|1.0|353183
0:00:00:00.437|0:00:00:00.006|154229|**|17|89|1.03764651831122054197e+294|1.03764651831122040016e+294|1.0|352926
0:00:00:00.444|0:00:00:00.007|156169|**|17|90|1.01931775163538164169e+217|1.01931775163538151929e+217|1.0|351731
0:00:00:00.450|0:00:00:00.006|159254|**|17|91|1.80243813284596224935e+304|1.8024381328459620058e+304|1.0|353897
0:00:00:00.452|0:00:00:00.002|161123|**|17|92|2.48452133154267074469e+300|2.4845213315426704474e+300|1.0|356466
0:00:00:00.455|0:00:00:00.003|161386|**|17|93|1.60159877161424463415e+302|1.60159877161424444376e+302|1.0|354694
0:00:00:00.456|0:00:00:00.001|161574|**|17|94|1.18800431472334704096e+213|1.18800431472334689145e+213|1.0|354329
0:00:00:00.459|0:00:00:00.003|162108|**|17|95|1.46857009050744854606e+259|1.46857009050744837528e+259|1.0|353176
0:00:00:00.460|0:00:00:00.001|162740|**|17|96|1.00180240129127753998e+201|1.00180240129127740402e+201|1.0|353782
0:00:00:00.470|0:00:00:00.010|162911|**|17|97|1.05427829800399293672e+263|1.05427829800399279686e+263|1.0|346619
0:00:00:00.480|0:00:00:00.010|168796|**|17|98|1.15494839941987284819e+226|1.15494839941987271667e+226|1.0|351658
0:00:00:00.497|0:00:00:00.017|173301|**|17|99|1.1255236032922892388e+241|1.12552360329228909069e+241|1.0|348694
0:00:00:00.502|0:00:00:00.005|182354|**|17|100|1.37233284717028864612e+222|1.37233284717028848566e+222|1.0|363255
0:00:00:00.508|0:00:00:00.006|183694|**|17|101|2.17769305580822362987e+304|2.17769305580822338614e+304|1.0|361602
0:00:00:00.514|0:00:00:00.006|186604|**|17|102|1.31982417337731764241e+281|1.31982417337731748119e+281|1.0|363042
0:00:00:00.519|0:00:00:00.005|188034|**|17|103|1.01434923413539603519e+282|1.01434923413539590617e+282|1.0|362300
0:00:00:00.522|0:00:00:00.003|189750|**|17|104|2.37322072384684994248e+306|2.37322072384684963045e+306|1.0|363505
0:00:00:00.530|0:00:00:00.008|190821|**|17|105|1.32394606099905674703e+287|1.32394606099905657789e+287|1.0|360039
0:00:00:00.534|0:00:00:00.004|194502|**|17|106|1.34743609253710314329e+231|1.34743609253710297101e+231|1.0|364236
0:00:00:00.537|0:00:00:00.003|195365|**|17|107|1.01448074214334464106e+251|1.01448074214334451389e+251|1.0|363808
0:00:00:00.541|0:00:00:00.004|196853|**|17|108|1.60084379120498344816e+277|1.6008437912049832514e+277|1.0|363868
0:00:00:00.543|0:00:00:00.002|198667|**|17|109|1.18225547643589664876e+229|1.18225547643589651411e+229|1.0|365869
0:00:00:00.548|0:00:00:00.005|198737|**|17|110|4.2763359877729573465e+307|4.27633598777295684733e+307|1.0|362658
0:00:00:00.552|0:00:00:00.004|200898|**|17|111|1.04664229478987052689e+242|1.04664229478987040838e+242|1.0|363945
0:00:00:00.556|0:00:00:00.004|201749|**|17|112|2.43955774762410903611e+300|2.4395577476241087386e+300|1.0|362857
0:00:00:00.565|0:00:00:00.009|203658|**|17|113|1.57255592970593624383e+293|1.57255592970593606654e+293|1.0|360456
0:00:00:00.573|0:00:00:00.008|207732|**|17|114|1.26377524702632414274e+250|1.26377524702632398369e+250|1.0|362534
0:00:00:00.577|0:00:00:00.004|211169|**|17|115|1.02555214083184593619e+304|1.02555214083184581433e+304|1.0|365977
0:00:00:00.583|0:00:00:00.006|212544|**|17|116|1.04192517962118714515e+238|1.04192517962118700062e+238|1.0|364569
0:00:00:00.585|0:00:00:00.002|215550|**|17|117|2.12752504166590614706e+300|2.12752504166590584956e+300|1.0|368461
0:00:00:00.588|0:00:00:00.003|215643|**|17|118|1.06561414923650434472e+273|1.06561414923650422459e+273|1.0|366739
0:00:00:00.590|0:00:00:00.002|216285|**|17|119|1.12562699860438224068e+266|1.12562699860438209745e+266|1.0|366584
0:00:00:00.591|0:00:00:00.001|216693|**|17|120|2.2444173431059562414e+307|2.24441734310595599204e+307|1.0|366654
0:00:00:00.600|0:00:00:00.009|216851|**|17|121|1.51163676671828514316e+206|1.51163676671828496492e+206|1.0|361418
0:00:00:00.603|0:00:00:00.003|221424|**|17|122|1.07793535219845434708e+300|1.07793535219845419844e+300|1.0|367204
0:00:00:00.605|0:00:00:00.002|221779|**|17|123|3.17101668180469004314e+302|3.17101668180468966259e+302|1.0|366576
0:00:00:00.612|0:00:00:00.007|222217|**|17|124|2.58583738131335064483e+306|2.5858373813133503328e+306|1.0|363099
0:00:00:00.623|0:00:00:00.011|225844|**|17|125|1.38930871389093314445e+203|1.38930871389093297044e+203|1.0|362510
0:00:00:00.628|0:00:00:00.005|230714|**|17|126|1.55631051086293154591e+271|1.55631051086293135821e+271|1.0|367379
0:00:00:00.635|0:00:00:00.007|232324|**|17|127|1.08757987071312204599e+207|1.08757987071312190341e+207|1.0|365864
0:00:00:00.639|0:00:00:00.004|235668|**|17|128|1.10971585002955783997e+266|1.10971585002955769674e+266|1.0|368807
0:00:00:00.642|0:00:00:00.003|236236|**|17|129|2.7399054414468558456e+303|2.73990544144685554094e+303|1.0|367968
0:00:00:00.643|0:00:00:00.001|236540|**|17|130|2.19580233532153664896e+300|2.19580233532153635167e+300|1.0|367869
0:00:00:00.647|0:00:00:00.004|236680|**|17|131|2.78347829770385524184e+306|2.78347829770385493002e+306|1.0|365811
0:00:00:00.657|0:00:00:00.010|238321|**|17|132|1.2790592123752721435e+216|1.27905921237527199041e+216|1.0|362741
0:00:00:00.661|0:00:00:00.004|243232|**|17|133|1.6990643033019567497e+221|1.69906430330195654912e+221|1.0|367975
0:00:00:00.663|0:00:00:00.002|244019|**|17|134|1.06415017067946343469e+276|1.06415017067946331174e+276|1.0|368052
0:00:00:00.668|0:00:00:00.005|244025|**|17|135|1.19771475574120704967e+219|1.1977147557412068929e+219|1.0|365306
0:00:00:00.671|0:00:00:00.003|246058|**|17|136|2.07128511529100462558e+304|2.07128511529100438207e+304|1.0|366703
0:00:00:00.673|0:00:00:00.002|246636|**|17|137|2.59393234031146993948e+306|2.59393234031146962766e+306|1.0|366472
0:00:00:00.679|0:00:00:00.006|246703|**|17|138|1.00195337328506553248e+208|1.00195337328506541842e+208|1.0|363332
0:00:00:00.692|0:00:00:00.013|250326|**|17|139|3.22188854814824254943e+305|3.22188854814824215976e+305|1.0|361742
0:00:00:00.695|0:00:00:00.003|256822|**|17|140|1.37122493603298244765e+247|1.37122493603298229239e+247|1.0|369528
0:00:00:00.702|0:00:00:00.007|257314|**|17|141|1.57931819376034624693e+302|1.57931819376034605654e+302|1.0|366544
0:00:00:00.710|0:00:00:00.008|260853|**|17|142|2.76247170254871274395e+306|2.76247170254871243213e+306|1.0|367398
0:00:00:00.717|0:00:00:00.007|264073|**|17|143|1.12805715931988562899e+220|1.12805715931988550355e+220|1.0|368302
0:00:00:00.720|0:00:00:00.003|266338|**|17|144|1.1148746454517305469e+133|1.11487464545173042081e+133|1.0|369913
0:00:00:00.727|0:00:00:00.007|267154|**|17|145|1.00235307361433404599e+306|1.00235307361433389008e+306|1.0|367474
0:00:00:00.731|0:00:00:00.004|270507|**|17|146|1.58824575783221474487e+234|1.58824575783221456836e+234|1.0|370050
0:00:00:00.734|0:00:00:00.003|270888|**|17|147|2.62484315098278413289e+306|2.62484315098278382107e+306|1.0|369057
0:00:00:00.741|0:00:00:00.007|272111|**|17|148|4.20336502432900634747e+304|4.20336502432900586045e+304|1.0|367221
0:00:00:00.745|0:00:00:00.004|275925|**|17|149|1.09102277837831224431e+300|1.09102277837831209555e+300|1.0|370369
0:00:00:00.751|0:00:00:00.006|277475|**|17|150|1.02141408497343012611e+298|1.02141408497343000999e+298|1.0|369474
0:00:00:00.753|0:00:00:00.002|279504|**|17|151|1.62722020141106314822e+304|1.62722020141106290462e+304|1.0|371187
0:00:00:00.765|0:00:00:00.012|279558|**|17|152|2.12833536967631961893e+301|2.12833536967631938106e+301|1.0|365435
0:00:00:00.768|0:00:00:00.003|285745|**|17|153|1.18258376240668873361e+303|1.18258376240668858139e+303|1.0|372063
0:00:00:00.770|0:00:00:00.002|285874|**|17|154|1.08922166034780434852e+105|1.08922166034780422123e+105|1.0|371264
0:00:00:00.781|0:00:00:00.011|286563|**|17|155|1.5133132169333487425e+296|1.51331321693334856096e+296|1.0|366918
0:00:00:00.792|0:00:00:00.011|292661|**|17|156|1.2011172837865029346e+235|1.20111728378650279333e+235|1.0|369521
0:00:00:00.796|0:00:00:00.004|298259|**|17|157|1.2244288581068922372e+294|1.22442885810689209538e+294|1.0|374697
0:00:00:00.802|0:00:00:00.006|299159|**|17|158|1.25368040202503814987e+293|1.2536804020250379726e+293|1.0|373016
0:00:00:00.805|0:00:00:00.003|301236|**|17|159|1.23076113342259014669e+247|1.23076113342258999143e+247|1.0|374206
0:00:00:00.809|0:00:00:00.004|301472|**|17|160|2.44040959835967074783e+306|2.44040959835967043601e+306|1.0|372647
0:00:00:00.811|0:00:00:00.002|302982|**|17|161|1.13259838496746473507e+226|1.13259838496746460356e+226|1.0|373590
0:00:00:00.815|0:00:00:00.004|303742|**|17|162|3.31792816211933133999e+302|3.31792816211933095944e+302|1.0|372689
0:00:00:00.819|0:00:00:00.004|305108|**|17|163|1.14388674485007364029e+204|1.14388674485007350097e+204|1.0|372537
0:00:00:00.821|0:00:00:00.002|306553|**|17|164|1.08357819693285744526e+219|1.08357819693285728848e+219|1.0|373389
0:00:00:00.834|0:00:00:00.013|307180|**|17|165|1.19043288742837693929e+294|1.19043288742837679748e+294|1.0|368321
VM-steps: 2319
Run Time: real 0.849 user 0.593750 sys 0.000000
Runtime error: interrupted (9)
sqlite>

You will note that %!.18g has no errors after 17 million reandom tests.

You will also note that $!.17g shows errors that are consistently 1 ulp.

When I compile such that LONGDOUBLE_TYPE is __float128 (quadruple precision) the results are pretty much the same %!.17g shows a whole whack of 1 ulp errors so this is an algorithm problem rather than a precision problem.

On Windows x64 using a compiler that treats all "long double" as "double" values mo more than 14 significant digits be tripped real->text->real no matter what format is used.

Note that the output format can be "fixed" so that %.17g is fully trippable by simply coding the constants in printf as long double type instead of doubles.

Index: src/printf.c
==================================================================
--- src/printf.c
+++ src/printf.c
@@ -591,17 +591,17 @@
             break;
           }

           /* Normalize realvalue to within 10.0 > realvalue >= 1.0 */
           if( ALWAYS(realvalue>0.0) ){
-            LONGDOUBLE_TYPE scale = 1.0;
-            while( realvalue>=1e100*scale && exp<=350){ scale*=1e100;exp+=100;}
-            while( realvalue>=1e10*scale && exp<=350 ){ scale*=1e10; exp+=10; }
-            while( realvalue>=10.0*scale && exp<=350 ){ scale *= 10.0; exp++; }
+            LONGDOUBLE_TYPE scale = 1.0l;
+            while( realvalue>=1e100l*scale && exp<=350){ scale*=1e100l;exp+=100;}
+            while( realvalue>=1e10l*scale && exp<=350 ){ scale*=1e10l; exp+=10; }
+            while( realvalue>=10.0l*scale && exp<=350 ){ scale *= 10.0l; exp++; }
             realvalue /= scale;
-            while( realvalue<1e-8 ){ realvalue *= 1e8; exp-=8; }
-            while( realvalue<1.0 ){ realvalue *= 10.0; exp--; }
+            while( realvalue<1e-8l ){ realvalue *= 1e8l; exp-=8; }
+            while( realvalue<1.0l ){ realvalue *= 10.0l; exp--; }
             if( exp>350 ){
               bufpt = buf;
               buf[0] = prefix;
               memcpy(buf+(prefix!=0),"Inf",4);
               length = 3+(prefix!=0);

The following also works without requiring the compiler to support long double constants though it is slightly slower since it has to "loop around" more. Interestingly it found 1 value that could not tripout of several hundred million random tests: exactvalue='1.00095672981729354948e-292', exactnewvalue='1.00095672981729343878e-292', distance=1.0

Index: src/printf.c
==================================================================
--- src/printf.c
+++ src/printf.c
@@ -591,15 +591,12 @@
             break;
           }

           /* Normalize realvalue to within 10.0 > realvalue >= 1.0 */
           if( ALWAYS(realvalue>0.0) ){
-            LONGDOUBLE_TYPE scale = 1.0;
-            while( realvalue>=1e100*scale && exp<=350){ scale*=1e100;exp+=100;}
-            while( realvalue>=1e10*scale && exp<=350 ){ scale*=1e10; exp+=10; }
-            while( realvalue>=10.0*scale && exp<=350 ){ scale *= 10.0; exp++; }
-            realvalue /= scale;
+            while( realvalue>=1e8 ){ realvalue /= 1e8; exp+=8; }
+            while( realvalue>=10.0 ){ realvalue /= 10.0; exp++; }
             while( realvalue<1e-8 ){ realvalue *= 1e8; exp-=8; }
             while( realvalue<1.0 ){ realvalue *= 10.0; exp--; }
             if( exp>350 ){
               bufpt = buf;
               buf[0] = prefix;

This also works if you do not mind hauling in the math library. It does, however, appear to have a slightly higher error rate that either of the previous versions.

Index: src/printf.c
==================================================================
--- src/printf.c
+++ src/printf.c
@@ -8,11 +8,11 @@
 ** routines format strings much like the printf() from the standard C
 ** library, though the implementation here has enhancements to support
 ** SQLite.
 */
 #include "sqliteInt.h"
-
+#include <math.h>
 /*
 ** Conversion types fall into various categories as defined by the
 ** following enumeration.
 */
 #define etRADIX       0 /* non-decimal integer types.  %x %o */
@@ -591,17 +591,12 @@
             break;
           }

           /* Normalize realvalue to within 10.0 > realvalue >= 1.0 */
           if( ALWAYS(realvalue>0.0) ){
-            LONGDOUBLE_TYPE scale = 1.0;
-            while( realvalue>=1e100*scale && exp<=350){ scale*=1e100;exp+=100;}
-            while( realvalue>=1e10*scale && exp<=350 ){ scale*=1e10; exp+=10; }
-            while( realvalue>=10.0*scale && exp<=350 ){ scale *= 10.0; exp++; }
-            realvalue /= scale;
-            while( realvalue<1e-8 ){ realvalue *= 1e8; exp-=8; }
-            while( realvalue<1.0 ){ realvalue *= 10.0; exp--; }
+            exp = floorl(log10l(realvalue));
+            realvalue /= powl(10.0, exp);
             if( exp>350 ){
               bufpt = buf;
               buf[0] = prefix;
               memcpy(buf+(prefix!=0),"Inf",4);
               length = 3+(prefix!=0);

Overall, the performance is somewhat similar between all versions.

I prefer the first one (simply changing all the constants to type l (long double). This might not be portable to all compilers though (I have no idea since apparently some that support 64-bit integers do not support 64-bit integer constants) and I have no idea what the applicable standard says.

As for when using crappy compilers from Microsoft that do do away with half-a-century of computational accuracy and precision by treating long double as double, there does not seem to be much that can be done about that.

I will note, however, that when using 64-bit python on Windows x64 if you just "re-compile" the Python.exe/PythonW.exe process starter with a compiler that works properly (eg, MinGW) then the whole issue is bypassed and long double works just peachy-keen in loaded dll's that were not castrated at compile time by a Microsoft compiler (ie, were compiled with a compiler that works properly).

I note that it appears to be the "exe creating the process" that needs to run an appropriate crt initializer that sets the FPU modes correctly as Windows fubarifies the settings for each new process (or perhaps it is the Microsoft compiler CRT process initializer that fubarifies the settings).

So, I have just changed the constant specifications to have an l (lowercase l) suffix and changed the first attempted format string in func.c sqlite3QuoteValue to '%!.17g', which ought to catch most of the conversions and just leave a few to go to the longer %!.20e format.

Nothing much can be done for versions created by defective Microsoft compilers on Windows x64, however (or any others that do not support long double), though perhaps the AtoF could be re-worked to do a better job when long double is not available.

Perhaps one of these changes or something similar can be incorporated in the distribution code. I do not know how to "fix" the AtoF code so that it works better when in a castrated (no long double) environment.

I suspect (but haven't quite the experience to say straight off) that 1.00095672981729354948e-292 is a denormalized value; those are often very good for stressing code that prints floating point numbers. When I run the value through the renderer in Tcl (which is designed — not by me! but by someone who was very careful and knowledgeable — to explicitly always use the minimal exact representation by default) I get this:

  % set x 1.00095672981729354948e-292
  1.00095672981729354948e-292
  % expr {double($x)}
  1.0009567298172935e-292

That makes me suspect that the final four decimal digits of the rendered mantissa are essentially noise, adding no actual bits of information.

The "extra digits" are added to ensure that the value is sufficiently represented. When I run the '%.17g' values though the Python text->float converter, they come out correctly. The error is in the SQLite3 AtoF routine.

sqlite> select * from bad;
┌─────┬───────────────────────┬───────────────────────────┬───────────────────────┬──────┬───────────┬───────────────────────────────┬───────────────────────────────┐
│ row │         value         │       failedformat        │       newvalue        │ ulps │ errortype │          exactvalue           │         exactnewvalue         │
├─────┼───────────────────────┼───────────────────────────┼───────────────────────┼──────┼───────────┼───────────────────────────────┼───────────────────────────────┤
│ 1   │ 1.00026208676053e-292 │ '1.0002620867605284e-292' │ 1.00026208676053e-292 │ 1.0  │ 'AtoF'    │ '1.00026208676052844677e-292' │ '1.00026208676052833542e-292' │
│ 2   │ 1.00061950172855e-292 │ '1.0006195017285523e-292' │ 1.00061950172855e-292 │ 1.0  │ 'AtoF'    │ '1.00061950172855234687e-292' │ '1.00061950172855223541e-292' │
│ 3   │ 1.00075723361897e-292 │ '1.0007572336189666e-292' │ 1.00075723361897e-292 │ 1.0  │ 'AtoF'    │ '1.00075723361896664893e-292' │ '1.00075723361896653791e-292' │
│ 4   │ 1.00088881378659e-292 │ '1.0008888137865871e-292' │ 1.00088881378659e-292 │ 1.0  │ 'AtoF'    │ '1.00088881378658714612e-292' │ '1.00088881378658703467e-292' │
│ 5   │ 1.00101274041036e-292 │ '1.0010127404103561e-292' │ 1.00101274041036e-292 │ 1.0  │ 'AtoF'    │ '1.00101274041035614797e-292' │ '1.00101274041035603684e-292' │
│ 6   │ 1.00105900606835e-292 │ '1.0010590060683496e-292' │ 1.00105900606835e-292 │ 1.0  │ 'AtoF'    │ '1.00105900606834964693e-292' │ '1.00105900606834953548e-292' │
│ 7   │ 1.00155394638467e-292 │ '1.0015539463846738e-292' │ 1.00155394638467e-292 │ 1.0  │ 'AtoF'    │ '1.00155394638467384795e-292' │ '1.00155394638467373693e-292' │
│ 8   │ 1.00162605011115e-292 │ '1.0016260501111453e-292' │ 1.00162605011115e-292 │ 1.0  │ 'AtoF'    │ '1.00162605011114534507e-292' │ '1.00162605011114523318e-292' │
│ 9   │ 1.00174668884533e-292 │ '1.0017466888453286e-292' │ 1.00174668884533e-292 │ 1.0  │ 'AtoF'    │ '1.00174668884532864566e-292' │ '1.00174668884532853453e-292' │
│ 10  │ 1.00181542178059e-292 │ '1.0018154217805888e-292' │ 1.00181542178059e-292 │ 1.0  │ 'AtoF'    │ '1.00181542178058884692e-292' │ '1.0018154217805887359e-292'  │
│ 11  │ 1.00192580664293e-292 │ '1.0019258066429285e-292' │ 1.00192580664293e-292 │ 1.0  │ 'AtoF'    │ '1.00192580664292854694e-292' │ '1.00192580664292843581e-292' │
│ 12  │ 1.00207570261607e-292 │ '1.0020757026160719e-292' │ 1.00207570261607e-292 │ 1.0  │ 'AtoF'    │ '1.00207570261607194922e-292' │ '1.00207570261607183765e-292' │
└─────┴───────────────────────┴───────────────────────────┴───────────────────────┴──────┴───────────┴───────────────────────────────┴───────────────────────────────┘

here it is in loadable format:

CREATE TABLE badtrippers
(
    value          real not null primary key,
    failedformat   text not null,
    newvalue       real not null,
    exactvalue     text not null,
    exactnewvalue  text not null,
    errortype      text not null
) without rowid;
INSERT INTO badtrippers VALUES(1.0002620867605284467e-292,'1.0002620867605284e-292',1.0002620867605283354e-292,'1.00026208676052844677e-292','1.00026208676052833542e-292','AtoF');
INSERT INTO badtrippers VALUES(1.0006195017285523468e-292,'1.0006195017285523e-292',1.0006195017285522354e-292,'1.00061950172855234687e-292','1.00061950172855223541e-292','AtoF');
INSERT INTO badtrippers VALUES(1.0007572336189666489e-292,'1.0007572336189666e-292',1.0007572336189665379e-292,'1.00075723361896664893e-292','1.00075723361896653791e-292','AtoF');
INSERT INTO badtrippers VALUES(1.0008888137865871461e-292,'1.0008888137865871e-292',1.0008888137865870346e-292,'1.00088881378658714612e-292','1.00088881378658703467e-292','AtoF');
INSERT INTO badtrippers VALUES(1.0010127404103561479e-292,'1.0010127404103561e-292',1.0010127404103560368e-292,'1.00101274041035614797e-292','1.00101274041035603684e-292','AtoF');
INSERT INTO badtrippers VALUES(1.0010590060683496469e-292,'1.0010590060683496e-292',1.0010590060683495354e-292,'1.00105900606834964693e-292','1.00105900606834953548e-292','AtoF');
INSERT INTO badtrippers VALUES(1.0015539463846738479e-292,'1.0015539463846738e-292',1.0015539463846737369e-292,'1.00155394638467384795e-292','1.00155394638467373693e-292','AtoF');
INSERT INTO badtrippers VALUES(1.001626050111145345e-292,'1.0016260501111453e-292',1.0016260501111452331e-292,'1.00162605011114534507e-292','1.00162605011114523318e-292','AtoF');
INSERT INTO badtrippers VALUES(1.0017466888453286456e-292,'1.0017466888453286e-292',1.0017466888453285345e-292,'1.00174668884532864566e-292','1.00174668884532853453e-292','AtoF');
INSERT INTO badtrippers VALUES(1.0018154217805888469e-292,'1.0018154217805888e-292',1.0018154217805887359e-292,'1.00181542178058884692e-292','1.0018154217805887359e-292','AtoF');
INSERT INTO badtrippers VALUES(1.0019258066429285469e-292,'1.0019258066429285e-292',1.0019258066429284358e-292,'1.00192580664292854694e-292','1.00192580664292843581e-292','AtoF');
INSERT INTO badtrippers VALUES(1.0020757026160719492e-292,'1.0020757026160719e-292',1.0020757026160718376e-292,'1.00207570261607194922e-292','1.00207570261607183765e-292','AtoF');
CREATE VIEW bad
as select row_number() over () as row,
          value,
          failedformat,
          newvalue,
          ulps(value, newvalue) as ulps,
          errortype,
          exactvalue,
          exactnewvalue
     from badtrippers;

Here is bog standard python that runs a non-transactional SQL statement which generates failures (success == 0) and periodic status (success == 1). Once you "fix" the SQLite3 printf.c so that it uses long double constants, then on a long double supporting platform it generates only a few errors for hundred million random tests. You can use the same generator in any language.

from datetime import datetime
from sqlite3 import connect
from time import time

sql = '''
create table if not exists badtrippers
(
    value          real not null primary key,
    testformat     text not null,
    newvalue       real not null,
    errortype      text not null
) without rowid;
'''.strip()

generator = '''
  with recursive
       randomreal(value) as not materialized
       (
           select random()/9223372036854775808.0 * pow(10, cast(random()/9223372036854775808.0*308 as integer))
        union all
           select case when random() < 0
                       then trunc(random()/9223372036854775808.0 * pow(2, abs(cast(random()/9223372036854775808.0*63 as integer))))
                       else random()/9223372036854775808.0 * pow(10, cast(random()/9223372036854775808.0*308 as integer))
                   end
             from randomreal
       ),
       step1(row, value, testformat) as not materialized
       (
           select row_number() over (rows between unbounded preceding and current row),
                  value,
                  format('%!.17g', value)
             from randomreal
       ),
       step2(row, value, newvalue, testformat) as not materialized
       (
           select row,
                  value,
                  cast(testformat as real),
                  testformat
             from step1
       )
select row,
       value == newvalue as success,
       coalesce(sum(1) filter (where value != newvalue) over (rows between unbounded preceding and current row), 0) as badcount,
       value,
       testformat,
       newvalue
  from step2
 where value != newvalue
    or row % 1000000 == 0
    or row == 1
'''.strip()

elapsedsince = lambda stamp: datetime.utcfromtimestamp(time()-stamp).time().isoformat()[:12]

db = connect('badtrippers.db', isolation_level=None)
db.executescript(sql)

stmtstart = time()
for row in db.execute(generator):
    if row[0] == 1:
        print(f'Time to First Row={elapsedsince(stmtstart)}')
        stmtstart = time()
        stepstart = time()
        if row[1]:
            continue
    if row[1]:
        print(f'Elapsed={elapsedsince(stmtstart)}; Step={elapsedsince(stepstart)}; Rows={row[0]}; BadCount={row[2]};')
        stepstart = time()
    else:
        errortype = 'AtoF' if float(row[4]) == row[3] else 'FtoA'
        print(f'** ErrorType={errortype}; Value={row[3]}; NewValue={row[5]}; Format={row[4]};')
        db.execute('insert or ignore into badtrippers values (?,?,?,?)', (row[3], row[4], row[5], errortype))

Fixed those errors too. Yet more missing declaration of type suffix for long double constants:

Index: src/util.c
==================================================================
--- src/util.c
+++ src/util.c
@@ -603,14 +603,14 @@
       if( e>307 ){                                      /*OPTIMIZATION-IF-TRUE*/
         if( e<342 ){                                    /*OPTIMIZATION-IF-TRUE*/
           LONGDOUBLE_TYPE scale = sqlite3Pow10(e-308);
           if( esign<0 ){
             result = s / scale;
-            result /= 1.0e+308;
+            result /= 1.0e+308L;
           }else{
             result = s * scale;
-            result *= 1.0e+308;
+            result *= 1.0e+308L;
           }
         }else{ assert( e>=342 );
           if( esign<0 ){
             result = 0.0*s;
           }else{

...on platforms supporting long double.

Would platforms not supporting long double accept the long double L-constants like 1e100L ?

The C89 standard as far as I know does require that "long double" be parsed, and also that the long double constant specifier be "parsed". That said, the implementation is free to ignore the "long" part and treat it as a double and then also use the double ABI (since the variables are really doubles).

Some early compilers moaned about the L mdoifier, even though they did treat long double as an extended precision type (eg, GCC 2.95, though that bug was fixedfor version 3 onwards).

The code already had some L type suffixes, but only in code enabled for Microsoft compilers.

After running a few billion tests the following numbers fail to trip '%!.17g':

┌───────────────────────────────┐
│         quote(value)          │
├───────────────────────────────┤
│ '1.00001558487316414983e-292' │
│ '1.00006969795643864927e-292' │
│ '1.00009516185237164762e-292' │
│ '1.00012495647664844474e-292' │
│ '1.00019444519253774866e-292' │
│ '1.00020441408956564568e-292' │
│ '1.00028398783673754923e-292' │
│ '1.00030320168236534891e-292' │
│ '1.00034441782267834782e-292' │
│ '1.00035698661013034566e-292' │
│ '1.00041020614617364695e-292' │
│ '1.00043009383305794802e-292' │
│ '1.00047417142008164688e-292' │
│ '1.00051895459927844898e-292' │
│ '1.00052612612965144997e-292' │
│ '1.00062423747236284581e-292' │
│ '1.00070516046387114985e-292' │
│ '1.00072543398189144937e-292' │
│ '1.00079242715725684584e-292' │
│ '1.00082852801608354989e-292' │
│ '1.00088471655322914935e-292' │
│ '1.00091309867223774582e-292' │
│ '1.00091842566521974533e-292' │
│ '1.00092211709372514989e-292' │
│ '1.00100013736846794789e-292' │
│ '1.00112387470683244952e-292' │
│ '1.00117908220378314609e-292' │
│ '1.0011965675491489488e-292'  │
│ '1.00139179902412494942e-292' │
│ '1.00143458668870224731e-292' │
│ '1.00158754491543934821e-292' │
│ '1.00162440126608924659e-292' │
│ '1.00163984636159244872e-292' │
│ '1.0018172806907812459e-292'  │
│ '1.00182615719880474774e-292' │
│ '1.00186308488039734821e-292' │
│ '1.00191913185909024786e-292' │
│ '1.00192287785665824577e-292' │
│ '1.00193021634724774781e-292' │
│ '1.00206376119604174805e-292' │
└───────────────────────────────┘

I made some more modifications to both the CTE and the Python driver program so that I can characterize the error location. Basically, the CTE generates random real numbers of random significand bit pattern and random exponent. They are then newvalue = cast(format('%!.17g', value) as real) if newvalue == value then the round trip was successful.

If the round trip was not successful, then the failedformat = format('%!.17g', value), value, and newvalue are passed to Python and Python computes testvalue = float(failedformat). If testvalue == value then the problem is in the SQLite3 AtoF code, if testvalue == newvalue then the problem is in the SQLite3 FtoA code, else if they are all different then the problem location is still a mystery.

So far, the errors appear all to be located in the AtoF code (once the constants in the FtoA are updated to long double type).

This is only for platforms where long double is supported (__float80 or longer).