Test script

#!/bin/sh

db="test.db"

rm -f $db
${1:-sqlite3} $db <<-EOF
   select load_extension("libspatialite.so");

   SELECT sqlite_version();
   SELECT spatialite_version();
   SELECT proj4_version();
   SELECT geos_version();

   SELECT InitSpatialMetadata();
   CREATE TABLE test_geom (
      id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      measured_value DOUBLE NOT NULL
   );
   SELECT AddGeometryColumn('test_geom', 'the_geom', 4326, 'POINT', 'XY');
   SELECT CreateSpatialIndex('test_geom', 'the_geom');
   INSERT INTO test_geom (name, measured_value, the_geom)
      VALUES ("first point", 1.23456, GeomFromText("POINT(1.01 2.02)", 4326));

   SELECT "CheckSpatialIndex()";
   SELECT CheckSpatialIndex();

   SELECT "RecoverSpatialIndex()";
   SELECT RecoverSpatialIndex();

   SELECT "CheckSpatialIndex() once again";
   SELECT CheckSpatialIndex();

   SELECT MbrMinX(g."the_geom"), MbrMinY(g."the_geom"),
         MbrMaxX(g."the_geom"), MbrMaxY(g."the_geom"),
         i.xmin, i.ymin, i.xmax, i.ymax
      FROM "test_geom" AS g
      LEFT JOIN "idx_test_geom_the_geom" AS i ON (g.ROWID = i.pkid);
EOF

Results

SQLite 3.7.13

Incorrect result. CheckSpatialIndex() = 0.

3.7.13
4.1.0-beta
Rel. 4.7.1, 23 September 2009
3.3.3-CAPI-1.7.4
1
1
1
CheckSpatialIndex()
0
RecoverSpatialIndex()
1
CheckSpatialIndex() once again
0
1.01|2.02|1.01|2.02|1.00999999046326|2.01999998092651|1.01000010967255|2.02000021934509

SQLite 3.7.12

Correct result.

3.7.12.1
4.1.0-beta
Rel. 4.7.1, 23 September 2009
3.3.3-CAPI-1.7.4
1
1
1
CheckSpatialIndex()
1
RecoverSpatialIndex()
1
CheckSpatialIndex() once again
1
1.01|2.02|1.01|2.02|1.00999999046326|2.01999998092651|1.00999999046326|2.01999998092651

According to changelog, rounding of floating point numbers was changed in sqlite 3.7.13.

In the RTREE extension, when rounding 64-bit floating point numbers to 32-bit for storage, always round in a direction that causes the bounding box to get larger.

sandro added on 2013-01-23 13:53:39 UTC:
Fixed in the Fossil repo. now eval_rtree_enty() cosider a valid match even two slightly different values (sqlite >= 3.7.13 usually expands the RTree coordinate by a very small amount)