Demonstrates a possible rtree regression in sqlite 3.8.5

geo_bug.sql

-- 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;