Skip to content

Instantly share code, notes, and snippets.

@snej
Last active August 29, 2015 14:06
Show Gist options
  • Save snej/f76733c4a65902ab7b32 to your computer and use it in GitHub Desktop.
Save snej/f76733c4a65902ab7b32 to your computer and use it in GitHub Desktop.
Demonstrates a possible rtree regression in sqlite 3.8.5
-- This demonstrates what seems to be a regression in the SQLite version (3.8.5) in OS X 10.10 DP7.
-- The final 'select' statement at the end of the file returns zero rows, not three.
-- The query works fine in OS X 10.9.4 (SQLite 3.7.13) and in earlier versions of 10.10 up to DP6.
-- If I alter the bboxes table to be a regular (non-rtree) table, the bug goes away.
--
-- Run this like so:
-- sqlite3 -init geo_bug.sql
CREATE TABLE maps (
view_id INTEGER NOT NULL,
sequence INTEGER NOT NULL,
key TEXT NOT NULL ,
value TEXT,
fulltext_id INTEGER,
bbox_id INTEGER,
geokey BLOB);
CREATE VIRTUAL TABLE bboxes USING rtree (
rowid integer primary key,
x0, x1, y0, y1);
INSERT INTO "maps" VALUES(1,1,'"two"',NULL,NULL,NULL,NULL);
INSERT INTO "maps" VALUES(1,2,'"four"',NULL,NULL,NULL,NULL);
INSERT INTO "maps" VALUES(1,3,'"one"',NULL,NULL,NULL,NULL);
INSERT INTO "maps" VALUES(1,4,'"three"',NULL,NULL,NULL,NULL);
INSERT INTO "maps" VALUES(1,5,'"five"',NULL,NULL,NULL,NULL);
INSERT INTO "maps" VALUES(1,6,'"Portland"',NULL,NULL,NULL,NULL);
INSERT INTO "maps" VALUES(1,6,'null',NULL,NULL,1,'{"type":"Point","coordinates":[-122.68,45.52]}');
INSERT INTO "maps" VALUES(1,7,'"Austin"',NULL,NULL,NULL,NULL);
INSERT INTO "maps" VALUES(1,7,'null',NULL,NULL,2,'{"type":"Point","coordinates":[-97.75,30.25]}');
INSERT INTO "maps" VALUES(1,8,'"Mountain View"',NULL,NULL,NULL,NULL);
INSERT INTO "maps" VALUES(1,8,'null',NULL,NULL,3,'{"type":"Point","coordinates":[-122.08,37.39]}');
INSERT INTO "maps" VALUES(1,9,'null',NULL,NULL,4,'{"type":"Point","coordinates":[-113.91,45.52]}');
INSERT INTO "maps" VALUES(1,10,'null',NULL,NULL,5,'{"type":"Point","coordinates":[40.12,37.53]}');
INSERT INTO "maps" VALUES(1,11,'null',NULL,NULL,6,'{"type":"Point","coordinates":[-2.205,-80.98]}');
INSERT INTO "maps" VALUES(1,12,'null',NULL,NULL,7,'{"type":"Polygon","coordinates":[[[-115,-10],[-115,12],[-90,12],[-90,-10],[-115,-10]]]}');
INSERT INTO "bboxes" VALUES(1,-122.680000305176,-122.679985046387,45.5199928283691,45.5200004577637);
INSERT INTO "bboxes" VALUES(2,-97.75,-97.75,30.25,30.25);
INSERT INTO "bboxes" VALUES(3,-122.080001831055,-122.079986572266,37.3899993896484,37.3900032043457);
INSERT INTO "bboxes" VALUES(4,-113.910003662109,-113.90998840332,45.5199928283691,45.5200004577637);
INSERT INTO "bboxes" VALUES(5,40.1199989318848,40.1200065612793,37.5299987792969,37.5300025939941);
INSERT INTO "bboxes" VALUES(6,-2.20500016212463,-2.20499992370605,-80.9800033569336,-80.9799880981445);
INSERT INTO "bboxes" VALUES(7,-115.0,-90.0,-10.0,12.0);
.print "Simple bbox query; three rows should be printed:"
select * from bboxes where x1 > -100 and x0 < 180 and y1 > 0 and y0 < 90;
.print ""
.print "Joined query; three rows should be printed:"
select * from bboxes,maps where x1 > -100 and x0 < 180 and y1 > 0 and y0 < 90 and maps.bbox_id=bboxes.rowid;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment