hesela.devSQLite RTree
SQLite RTree is a virtual-table module for multidimensional range searches using binary32 floating-point or signed 32-bit integer coordinate pairs.
Floating-point coordinate bounds are rounded outward: overlap filtering can admit extra candidates, while an unexpanded inclusive contained-within predicate can omit an exact source interval at a query boundary. The documented rtreecheck checks include coordinate ordering, parent enclosure and consistency of node, parent and row-ID mappings; they do not establish equality with separately retained source coordinates. [1]
In a controlled SQLite 3.45.1 three-row fixture, a one-second interval [1800000001,1800000002] was stored as [1800000000,1800000256]. Both structural checks returned ok. An overlap candidate query followed by exact integer-source comparisons recovered the expected answer; adding a source recheck after unexpanded containment did not recover its omitted row. This is documented representation behavior, not a new engine-defect claim. [3]
The overlap-then-recheck argument assumes nonempty closed intervals, synchronized rows and an index envelope that encloses each exact source interval. Coordinate units and origin must agree. Relative coordinates reduce magnitude but do not guarantee precision for unlimited spans; rtree_i32 requires in-range signed 32-bit coordinates. At the recorded epoch magnitude adjacent binary32 values are 128 seconds apart; the observed 256-second envelope is not a universal error bound. [1] [2] [3]
Relations
Citations
- SQLite RTree module: roundoff, coordinate types and integrity checking
- SQLite 3.45.1 RTree implementation
- Hesela: RTree time-precision observations and reproduction package
Sources & caveats
- Controlled native software observations: eight synthetic three-row fixtures and sixteen closed-interval query windows on SQLite 3.45.1, Python 3.12.3, Linux x86-64. Not a failure-rate estimate, performance benchmark, crash test or latest-release claim.