Hi, I think there has to be something wrong with the first insert that you do in a RTree table. If before copying the records to the virtual table you do a dummy insert then everything works OK.
sqlite> CREATE VIRTUAL TABLE test_rtree USING RTREE(pkid, xmin, xmax, ymin, ymax); sqlite> INSERT INTO test_rtree (pkid, xmin, xmax, ymin, ymax) values (-1, 1, 1, 1, 1); sqlite> INSERT INTO test_rtree (pkid, xmin, xmax, ymin, ymax) SELECT ROWID, x, x, y, y FROM test; sqlite> SELECT Count(*) FROM test_rtree WHERE xmin > 1000400.5 AND xmax < 1000450.5 AND ymin > 4000400.5 AND ymax < 4000450.5; 25 I know this is not a solution, but it may point to where the bug is. Bye 2008/9/7 Alessandro Furieri <[EMAIL PROTECTED]> > > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
