Hi,
I think I have found a bug in the RTree extension (v3.6.2)
a copy of the test database is available for download at:
http://www.gaia-gis.it/rtreebug.zip [1.5 MB]
---------
SQLite version 3.6.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
test
sqlite> PRAGMA table_info(test);
0|PK|INTEGER|99||0
1|x|DOUBLE|99||0
2|y|DOUBLE|99||0
sqlite> SELECT Count(*) FROM test;
250000
1) there is only a single table, containing 250,000 points
the whole dataset represents a square greed 500x500
(10m cell size); coord values are in the Gauss-Boaga
Monte Mario Italy 1940 reference system
sqlite> CREATE VIRTUAL TABLE test_rtree
...> USING RTREE(pkid, xmin, xmax, ymin, ymax);
sqlite> INSERT INTO test_rtree (pkid, xmin, xmax, ymin, ymax)
...> SELECT ROWID, x, x, y, y FROM test;
2) then I create an RTree, inserting rows copied from the other table
sqlite> SELECT Count(*) FROM test WHERE
...> x > 1000400.5 AND x < 1000450.5 AND
...> y > 4000400.5 AND y < 4000450.5;
25
sqlite> SELECT Count(*) FROM test_rtree WHERE
...> xmin > 1000400.5 AND xmax < 1000450.5 AND
...> ymin > 4000400.5 AND ymax < 4000450.5;
15
3) the second query [i.e. the one performed on the RTree]
is by far fastest than the other, but obviously returns
a wrong result
the right answer has to be 25, and not 15
sqlite> SELECT Count(*) FROM test_rtree_node;
10084
sqlite> UPDATE test_rtree SET xmin = xmin;
sqlite> SELECT Count(*) FROM test_rtree_node;
10255
sqlite> SELECT Count(*) FROM test_rtree WHERE
...> xmin > 1000400.5 AND xmax < 1000450.5 AND
...> ymin > 4000400.5 AND ymax < 4000450.5;
25
4) then I force the RTree to rebuild;
this time I get the expected answer (25)
----
Apparently the "UPDATE test_rtree SET xmin = xmin"
seems to be a very stupid operation (i.e. one
changing absolutely nothing), but this actually causes
the RTree to be rearranged in a different way.
As you can notice, number of tree-nodes changes
before and after performing this [and query
results change as well]
A behaviour such as the one I notice seems to be
quite suspect; so I suppose there is some bug
in the RTree module.
Thanks in advance.
Sandro
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
