Numeric JSON precision across SQLite runtimes
Silt keeps finite JavaScript numbers as JSON numbers. SQLite builds differ in how they parse decimal JSON and render binary floating-point values. A successful test on Node's SQLite does not establish equivalent behavior on Expo's older WebAssembly build.
SQL serialization fixes
The compiler's shared jsonNumber(sql) helper emits JSON numeric text with printf('%!.17g', ...) for REAL values and exact integer text for INTEGER values. SQL NULL becomes JSON null, and nonfinite arithmetic fails explicitly. The helper evaluates its input through one generated SQL expression rather than duplicating a potentially large expression in its SQL string.
jsonEachValue(alias) retrieves the original JSON token through the hidden json_each.json source and its fullkey. This avoids numeric formatting entirely when copying existing values in query multikey traversal, lookup traversal, aggregation expression array operations, and array updates. JSON construction continues to wrap numeric text with json(...), preserving its number type.
The affected output paths include arithmetic/conversion expressions, expression sums/averages, pipeline $sum/$avg, $inc/$mul, and array transformations. $stdDevPop/$stdDevSamp remain unsupported; this change adds no variance implementation. Original JSON text remains intact when operations can select it directly.
The audit also found two SQLite 3.50 correlation-resolution failures in $sortArray and $addToSet. Local materialized input/value relations keep their ORDER BY/GROUP BY subqueries from referencing a distant outer alias.
Verified improvements
The 98 probes generated by test/fixtures/numeric-precision-cases.js cover adjacent doubles around 1, a 17-digit fraction, maximum finite double, minimum subnormal, a small normal, large integral doubles, negative numbers, and zero. They exercise scalar arithmetic, arrays, filter/sort/set operations, multikey predicates, updates, grouping, empty input, and nonnumeric accumulator input.
All 98 probes passed on Node SQLite 3.53.3, Python SQLite 3.53.1, and actual Expo SQLite 3.50.3 in Chromium. Four of these regressions cover original-token copying and array equality for the known difficult values. Their MongoDB-representable operations also passed against the available live MongoDB server. The Expo adapter separately fixed large integer-looking JavaScript numbers being bound through its unsafe integer path. Additional actual-browser index tests compare hard-float predicates before and after backfill and trigger maintenance against the same engine's unindexed result.
A simple example explains why explicit serialization is necessary: on Python SQLite 3.53.1 and Expo SQLite 3.50.3, json_quote(1.0000000000000002) returns 1.0. Formatting this example with 17 significant digits before embedding it with json(...) preserves the value. The same change prevents the maximum finite double from being serialized as a decimal that JavaScript reads as infinity.
Proven limit in Expo SQLite 3.50.3 WebAssembly
The changes do not establish exact behavior for every finite double on that runtime. A wider deterministic 310-value probe found 75 canonical-key differences compared with Node/Python. Both decimal parsing and number formatting can be inaccurate in this older build:
Searching precisions 1 through 17 for the first decimal spelling that SQLite reads back as the same double is not a universal workaround: the older parser can accept the wrong decimal spelling, and some inputs have no match in that search. A binary key representation could remove formatting differences, but it would not repair an already incorrectly parsed number.
Full finite-double parity therefore needs a runtime with accurate decimal conversion, or a separately defined and enforced restricted numeric profile. This report does not claim that another untested SQLite version fixes every case. Changing stored key formats alone cannot fix an engine's parser, and migrating keys on open is insufficient when active connections with different conversion behavior share the same file.
Raw browser diagnostics are recorded in integration/expo/artifacts/float-parser-probes.json; the representative end-to-end results are in integration/expo/artifacts/web-results.json. The larger stress result was captured separately from the passing representative suite. Do not present the representative suite as proof of full finite-double or cross-engine canonical-key parity.
Reproduction
The numeric test replays generated SQL on Python SQLite 3.53+ and compares canonical key strings across Node/Python. This exact text comparison needs the newer floating-point conversion behavior; it is stricter than the representative SQL suite run on Expo. Older Python SQLite builds are reported as skipped locally. Set SILT_REQUIRE_PYTHON_SQLITE=1 to make a missing or older build fail, and optionally set SILT_PYTHON to the interpreter path.
CI builds a test-only shared SQLite library from the amalgamation in the locked better-sqlite3 dependency using scripts/ci-python-sqlite.sh. SILT_PYTHON_SQLITE_LIBRARY applies that library only to the Python subprocesses, and CI requires the cross-check to execute. This avoids accidentally using Ubuntu 24.04's SQLite 3.45.1, whose parser stack and floating-point text conversion differ from the verified engines. It adds no runtime dependency or extension to Silt.
The actual Expo validation runner regenerates the 98 representative probes from current compiler code. Native Expo device execution is a separate requirement; Chromium WebAssembly results are not native device results.