# SQLite RTree: Query Completeness and Time Precision

Controlled native software observations, synthetic inputs. Recorded on
2026-10-28 with SQLite 3.45.1 and Python 3.12.3 on Linux x86-64.

## Finding

The source interval [1800000001,1800000002] seconds became
[1800000000,1800000256] in a floating-coordinate RTree. Unexpanded inclusive containment
returned no row while rtreecheck and integrity_check both returned ok.
Overlap candidates followed by exact source checks returned row 1.

Rechecking cannot recover a row already rejected by the candidate query.
The completeness argument assumes nonempty closed intervals, synchronized index
rows, outward-enclosing bounds and consistent coordinate units/origins.
It is not a claim about every geometry, current version or query planner.

## Reproduce

reproduction.zip contains the unchanged study, native probes, observations,
tests and four source/policy snapshots. There are two checksum scopes:

- The downloadable SHA256SUMS covers the five public downloads: reproduction.zip,
  README.md, observations.json, padding-observations.json and summary.json.
- SHA256SUMS inside the ZIP covers its other fourteen members, including the
  original study README.md. That README is not the public download README;
  PUBLICATION.md inside the ZIP is this publication note.

After downloading all five files plus SHA256SUMS into one directory, check them
before extraction. No package installation or external dataset is required:

```sh
sha256sum --strict -c SHA256SUMS
mkdir unpacked
unzip reproduction.zip -d unpacked
cd unpacked
sha256sum --strict -c SHA256SUMS
python3 -B probe.py --output my-observations.json
python3 -B padding_probe.py --output my-padding-observations.json
```

The manifests detect byte mismatches against their recorded values, not a
malicious replacement of both files and manifests or scientific validity.

The probes refuse existing output files and create only private temporary
databases. They never accept a production database path. Read README.md inside
the archive for SQL, assumptions and primary sources.

Run the original 18 regression methods with `python3 -B -m unittest -v`.
Exact-replay tests compare runtime identity, plans and file hashes as well as
answers. They are specific to the recorded environment. A mismatch on another
environment is a new observation to investigate, not permission to overwrite
retained evidence. The probes themselves record the environment actually used.

summary.json is generated from the original observations and contains all eight
target rows, keyed by source case. The raw record also retains eight empty-window
controls and all five query paths. The separate padding control tests one literal
relative-padding implementation, not every outward-quantized strategy.

## What a passing check establishes

SQLite documents rtreecheck as checking coordinate ordering, parent enclosure
and consistency of the index's node, parent and row-ID mappings. These are
internal invariants, not an application-level comparison with the exact source
table. A passing check and a boundary omission can therefore coexist.
See https://www.sqlite.org/rtree.html#integrity_check_using_the_rtreecheck_sql_function.

## Limits and sources

These designed cases are not sampled incidents, failure rates, a benchmark,
a crash test or a newly discovered SQLite defect. Only one runtime was measured.
Its source ID has a distribution suffix; the upstream version-tagged source
explains the mechanism but is not claimed byte-identical to that library.

Primary sources, retrieval date and checksums are in manifest.json.
SQLite's public-domain notice covers its own source and documentation; it is
not a license for Hesela-authored work. Consult Hesela's cite-and-reuse page
for the latter before redistribution.

Related analysis:
https://hesela.com/analyses/sqlite-index-runtime-dependencies/