Inconsistent RTREE Query Results with Large Numeric Values in SQLite

Precision Limitations in RTREE Virtual Tables & Floating-Point Equality Comparisons

Issue Overview: RTREE Coordinate Storage & Numeric Representation Conflicts

The core problem arises when storing extremely large integer values in SQLite’s RTREE virtual tables and attempting exact equality comparisons against those values. RTREE virtual tables are designed for spatial indexing but are frequently repurposed for range queries on numeric data. These tables internally represent all coordinate values as 32-bit single-precision floating-point numbers. This becomes problematic when developers insert integers exceeding 16 million (2^24) or values requiring more than 7 significant digits of precision.

Consider the example schema:

CREATE TABLE v0 (c1 INT PRIMARY KEY, c2);
CREATE VIRTUAL TABLE v3 USING rtree(c4, c5, c6);

After inserting values like 18446744073709551615 (2^64 – 1) and 9223372036854775807 (2^63 – 1) into the RTREE table, subsequent queries demonstrate inconsistent behavior:

1. SELECT * FROM v18 WHERE c6 = 9223372036854775807 returns no matches
2. SELECT c6 = 9.223372036854776e+18 FROM v18 returns matches

This discrepancy occurs because the RTREE module silently converts all input values to 32-bit floats during storage. The 32-bit float format only preserves about 7 significant digits. When storing 9223372036854775807, the RTREE reduces this to 9.223372e18 (scientific notation), discarding lower-order digits. Queries using the integer literal 9223372036854775807 get converted to a 64-bit float (which retains full precision) before comparison, while the stored value has already lost precision. Thus, the equality check fails. However, using the scientific notation literal 9.223372036854776e+18 in the query forces SQLite to treat both operands as floats, enabling a match at the cost of precision loss in application logic.

The ALTER TABLE v3 RENAME TO v18 operation has no bearing on the issue but demonstrates how schema changes might inadvertently expose underlying data type limitations. The ORDER BY 18446744073709551615 clause in the original queries is syntactically valid but semantically nonsensical, as it attempts to sort by a constant value. This distracts from the core precision issue but highlights how large numeric literals can propagate through queries without validation.

Possible Causes: IEEE 754 Float Truncation & Implicit Casting Semantics

Three primary factors contribute to this behavior:

1. RTREE Coordinate Compression to 32-Bit Floats
The RTREE implementation maps all column values to 32-bit IEEE 754 floating-point numbers regardless of declared affinity or input formatting. This is an intentional design choice to minimize storage overhead for spatial coordinates. However, developers using RTREE for non-spatial data (e.g., timestamp ranges or large integer intervals) encounter silent precision loss. For example:

• Input: 9223372036854775807 (2^63 – 1)
• RTREE Storage: 9.223372e18 (only 7 significant digits retained)
• Implicit Cast to 64-Bit Float in Queries: 9223372036854775807.0 (retains 16 digits)

2. SQLite’s Flexible Type Affinity System
SQLite allows storing any value type in any column except INTEGER PRIMARY KEY. When comparing a numeric literal like 9223372036854775807 (interpreted as a 64-bit signed integer) against a stored 32-bit float, SQLite performs implicit casting to 64-bit float (REAL). This converts the stored 9.223372e18 to 9223372000000000000.0, while the literal becomes 9223372036854775807.0. These differ by ~3.6e15, causing equality checks to fail.

3. Scientific Notation Literals Bypass Integer Precision
Using scientific notation (e.g., 9.223372036854776e+18) in queries forces SQLite to treat the value as a float from the outset. Both operands in a26.c6 = 9.223372036854776e+18 are 64-bit floats, but the stored value has already lost precision. The comparison succeeds because the truncated stored value matches the lower-precision interpretation of the scientific notation literal. This creates an illusion of consistency while masking data corruption.

Troubleshooting Steps & Mitigation Strategies for Precision-Sensitive Data

1. Audit RTREE Usage for Precision Requirements
RTREE virtual tables are unsuitable for scenarios requiring exact integer storage beyond 16 million or 7 significant digits. For example:

• Unacceptable: Storing nanosecond timestamps (exceeds 2^64 by 2038)
• Acceptable: Geospatial coordinates (latitude/longitude with 6 decimal places)

Action:

-- Check maximum required precision for RTREE columns
SELECT 
  MAX(LENGTH(CAST(c4 AS TEXT)) - INSTR(CAST(c4 AS TEXT), '.')) AS max_decimal_c4,
  MAX(LENGTH(CAST(c5 AS TEXT)) - INSTR(CAST(c5 AS TEXT), '.')) AS max_decimal_c5,
  MAX(LENGTH(CAST(c6 AS TEXT)) - INSTR(CAST(c6 AS TEXT), '.')) AS max_decimal_c6 
FROM v18;

If any column requires more than 7 significant digits, migrate to an INTEGER-based table.

2. Replace RTREE with INTEGER Storage for Large Numbers
For exact integer comparisons, use explicit INTEGER columns with proper indexing:

-- Drop RTREE table
DROP TABLE v18;  

-- Create regular table with type affinity
CREATE TABLE v18_exact (
  id INTEGER PRIMARY KEY,
  c4 INTEGER,  -- INTEGER affinity enforces 64-bit signed storage
  c5 INTEGER,
  c6 INTEGER
);  

-- Re-insert data with exact integers
INSERT INTO v18_exact (c4, c5, c6) 
VALUES 
  (18446744073709551615, 9223372036854775807, 18446744073709551488),
  (4294967295, 9223372036854775807, 9223372036854775807),
  (127, 18446744071562067968, 18446744071562067968);  

-- Create covering index
CREATE INDEX idx_v18_exact ON v18_exact(c4, c5, c6);

3. Use Range Comparisons Instead of Equality Checks
When stuck with RTREE due to legacy constraints, replace exact equality with bounded range queries:

-- Original failing query
SELECT * FROM v18 WHERE c6 = 9223372036854775807;  

-- Revised query accounting for float imprecision
SELECT * 
FROM v18 
WHERE 
  c6 BETWEEN 9223372036854775807 - 1e11 AND 9223372036854775807 + 1e11 
  AND CAST(c6 AS INTEGER) = 9223372036854775807;

The BETWEEN clause uses RTREE’s native range query optimization, while the CAST ensures exact integer matching on filtered rows. Adjust the 1e11 delta based on your maximum expected precision loss.

4. Enforce Storage Precision Through Constraints
Prevent invalid data insertion by adding CHECK constraints:

CREATE VIRTUAL TABLE v3_prechecked USING rtree(
  c4, c5, c6,
  CHECK(
    ABS(c4 - CAST(c4 AS INTEGER)) = 0 AND
    ABS(c5 - CAST(c5 AS INTEGER)) = 0 AND
    ABS(c6 - CAST(c6 AS INTEGER)) = 0 AND
    c4 BETWEEN -9223372036854775808 AND 9223372036854775807 AND
    c5 BETWEEN -9223372036854775808 AND 9223372036854775807 AND
    c6 BETWEEN -9223372036854775808 AND 9223372036854775807 
  )
);  

This ensures stored values are integers within the 64-bit signed range. Attempting to insert 18446744073709551615 (exceeding 2^63-1) will now fail explicitly.

5. Normalize Scientific Notation in Application Layer
Convert all large numbers to exact integer strings before inserting or querying:

# Python example using decimal module
from decimal import Decimal

def sanitize_large_number(num):
    d = Decimal(num)
    if abs(d) > 2**24:
        return int(d)
    return float(d)

# Usage
sanitized_c6 = sanitize_large_number(9.223372036854776e+18)  # Returns 9223372036854776000

This ensures numbers exceeding 32-bit float precision get stored as integers where possible.

6. Utilize CAST Expressions in Queries
Force consistent typing between columns and literals:

-- Compare as integers
SELECT * 
FROM v18 
WHERE 
  CAST(c6 AS INTEGER) = 9223372036854775807 
  AND CAST(c5 AS INTEGER) = 9223372036854775807;  

-- Compare as floats with explicit rounding
SELECT * 
FROM v18 
WHERE 
  ROUND(c6, 0) = 9223372036854775807.0 
  AND ROUND(c5, 0) = 9223372036854775807.0;

7. Monitor Precision Loss with SQLite Math Functions
Implement diagnostic queries to detect precision loss in existing data:

-- Find rows where stored value differs from original integer
SELECT 
  c6, 
  c6 - 9223372036854775807 AS delta,
  ABS(c6 - 9223372036854775807) > 0 AS has_loss 
FROM v18;  

-- Result example:
-- 9.223372e18 | -36854775807.0 | 1 (indicating loss)

8. Migrate to SQLite 3.42.0+ for IEEE 754-2019 Support
SQLite versions ≥3.42.0 include optional support for strict affinity tables, enabling explicit 64-bit integer columns:

CREATE TABLE v18_strict (
  c4 INTEGER STRICT,
  c5 INTEGER STRICT,
  c6 INTEGER STRICT
);  

INSERT INTO v18_strict VALUES 
  (18446744073709551615, 9223372036854775807, 18446744073709551488);  
-- Succeeds if compiled with -DSQLITE_STRICT_SUBTYPE=1  

This bypasses SQLite’s traditional type affinity system, enforcing exact integer storage.

9. Recompile SQLite with 64-Bit RTREE Patches
For advanced use cases, modify the RTREE extension to store 64-bit doubles or integers:

1. Download SQLite amalgamation source
2. Edit rtree.c, changing RtreeValue from float to sqlite3_int64
3. Update all coordinate comparisons to use integer arithmetic
4. Recompile with -DSQLITE_RTREE_INT_ONLY=1

This approach requires thorough testing but allows RTREE tables to handle 64-bit integers natively.

10. Implement Application-Side Hashing for Exact Comparisons
Store cryptographic hashes of large numbers in separate columns for exact match queries:

ALTER TABLE v18 ADD COLUMN c6_hash BLOB;  

-- Update existing data
UPDATE v18 SET c6_hash = sha3(CAST(c6 AS INTEGER));  

-- Query using hash
SELECT * 
FROM v18 
WHERE 
  c6_hash = sha3(9223372036854775807) 
  AND c6 BETWEEN 9223372036854775807 - 1e11 AND 9223372036854775807 + 1e11;

This combines the performance of RTREE range queries with exact hash verification.

By methodically applying these strategies—ranging from schema redesign to query pattern adjustments—developers can mitigate precision loss issues in SQLite RTREE tables while preserving performance for large datasets. Critical systems should prioritize migrating away from RTREE for exact integer storage, reserving its use for genuine spatial data where 32-bit float precision is adequate.