Delegate range queries to SQL (applicable to old sqlite only)

[kno10]

On Sqlite3, and probably also other SQL backends, range queries can be written as follows:

SELECT
pos,
((pos + 0x800800800) & 0xFFF) - 0x800 as x, -- optional
(((pos + 0x800800800) >> 12) & 0xFFF) - 0x800 as y, -- optional
(((pos + 0x800800800) >> 24) & 0xFFF) - 0x800 as z, -- optional
data
FROM blocks where
((pos + 0x800800800) & 0xFFF) - 0x800 >= -3  AND -- minx
((pos + 0x800800800) & 0xFFF) - 0x800 <= 3 AND -- maxx
(((pos + 0x800800800) >> 12) & 0xFFF) - 0x800 >= -5 AND -- miny
(((pos + 0x800800800) >> 12) & 0xFFF) - 0x800 <= 1 AND -- maxy
pos >= (-12 << 24) - 0x800800 AND -- minz
pos <= (-4  << 24) + 0x7FF7FF; -- maxz

When pos is of type INTEGER PRIMARY KEY this can operate directly on the primary B-tree. With INT PRIMARY KEY, it finds all matching block positions on the index, and only retrieves results from the primary B-tree. You can use EXPLAIN QUERY PLAN to verify that it is using the row_id respectively the index.