SQLite imports: where exact identifiers lose digits

One numeric value, different import paths

We crossed five source strings with three parameter types and five schemas: 75 deliberately chosen insert attempts. An independent Python Decimal comparison checked exact numeric equality; a separate check compared original spelling. Every result below was read after closing and reopening the database, not from the insert's return value.

The plain digit string preserved the large integer. Adding .0 changed the conversion path. Parsing that decimal exactly into a Python integer before binding preserved the value as well. The parser in our harness first requires a finite integral value within SQLite's signed 64-bit range; it does not truncate fractions.

These are not failure-rate samples. The complete matrix includes scientific notation, leading zeros and 9223372036854775807, the signed 64-bit maximum. Of 75 attempts, 74 were accepted and one was rejected. Twenty-five accepted values differed numerically from their source strings under the exact oracle.

Three conversion boundaries to keep separate

Before binding: Python maps str, int and float to SQLite TEXT, INTEGER and REAL parameters. A float created from 9007199254740993 already represents 9007199254740992. No column can infer the discarded digit. The JSON records the bound type, decimal value and float hexadecimal representation. [6]

During affinity conversion: SQLite's type affinity can turn text into a number. Binary64 cannot represent every integer at this magnitude. This is different from SQLite INTEGER, which can preserve signed 64-bit integers exactly. [2] [3]

During strict validation: STRICT enforces accepted storage types but still applies conversion rules. In the pinned 3.45.1 source, OP_TypeCheck applies affinity before testing the resulting type. applyNumericAffinity can produce a real value and then try an integer conversion. That latter check compares the already represented real value with an integer, not with an arbitrary-precision copy of the source decimal. [4] [5]

The documentation describes lossless coercion. Our measured decimal-point example shows why that wording must not be read as a promise to preserve every digit of arbitrary decimal input. This result is scoped to the recorded runtime; it is not a new vulnerability claim or a test of every release. [1]

A valid type can still be the wrong identifier

ANY has different semantics in ordinary and STRICT tables. In our ordinary table, text 00123 became integer 123. STRICT ANY preserved the received string. That distinction matters when leading zeros are part of an identifier rather than formatting for a number. [1]

A two-input control inserted 9007199254740992.0 and 9007199254740993.0 as text into a STRICT INTEGER UNIQUE column. The first succeeded; the second hit SQLITE_CONSTRAINT_UNIQUE (2067). Both texts remained distinct in the corresponding STRICT ANY table. We used plain INSERT, not IGNORE or REPLACE, and retained the explicit error. [8]

STRICT still rejected the separate text inputs 1.5 and abc for INTEGER, with extended code 3091. The tested Python wrapper reported the symbolic name as unknown; evidence retains that raw name and the numeric code. A missing wrapper label is not an unknown database outcome. [8]

The backup faithfully retained the import

After closing the writer, we opened the source read-only and completed Python's SQLite backup API. Its last progress callback reported SQLITE_DONE with zero remaining pages. After reopening the destination, every matrix row, storage class and collision-control row matched the source. Its changed numbers were still changed. The source SHA-256 was identical before and after observation. [6]

Both quick_check and integrity_check returned ok for source and destination. Such checks inspect database consistency, not equality with an external import file. A successful copy is useful evidence about the copy; it cannot restore information discarded before storage. [7]

This complements the existing SQLite runtime-dependency experiment and backup acceptance analysis: preserve the source representation and interpretation rules, not only the final database.

Preserve first, then validate the interpretation

For archival ingestion, retain raw source text or bytes alongside deliberately parsed values. Bind identifiers as strings; when using STRICT ANY, enforce the intended input type in the importer. A STRICT TEXT column also preserved every text-bound fixture here, but converting an already rounded float to text did not recover the source.

For exact integral quantities, validate syntax, integrality and signed 64-bit range before integer binding. Keep larger values in an explicitly documented representation. Validate reopened results against an exact external oracle and expected identifiers. Do not use a SQL equality comparison that repeats the same conversion as the import.

Reproduce the observations

Download the stdlib-only harness, seven regression tests and complete JSON evidence. Keep the two Python files in one directory. The harness creates and removes only its own temporary databases; the output path is explicit.

python3 observe.py --output results.json
python3 test_observe.py

The tests encode this recorded behavior; a different runtime may require investigation rather than silently updating the expected values. The JSON stores measured numbers as strings with explicit types, avoiding a second large-integer conversion in a consumer.

Designed fixtures on Python 3.12.3 and one distribution SQLite build, not a failure-rate estimate or newly discovered vulnerability. No custom adapters, crash, WAL or concurrent writer. Pinned upstream source explains the path but is not claimed byte-identical to the distribution build. The source ID is recorded in the JSON. No experimental SQLite build or third-party package was installed.

Primary sources

1. SQLite: STRICT Tables, sections 2 and 3
2. SQLite: Datatypes, numeric affinity and storage classes
3. SQLite: Floating Point Numbers
4. SQLite version-3.45.1: applyNumericAffinity and OP_TypeCheck
5. SQLite version-3.45.1: sqlite3VdbeIntegerAffinity
6. Python 3.12 sqlite3: parameter types, backup and error attributes
7. SQLite: integrity_check scope
8. SQLite: extended result codes 3091 and 2067