Bug #30825 Problems when putting a non-spatial index on a GIS column
Submitted: 5 Sep 2007 10:43 Modified: 5 Nov 2007 3:06
Reporter: Hartmut Holzgraefe Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.0, 5.1 OS:Any
Assigned to: Georgi Kodinov CPU Architecture:Any
Tags: gis, INDEX

[5 Sep 2007 10:43] Hartmut Holzgraefe
Description:
When creating a regular index on a geometry type column
queries on that column fail if the optimizer decides to
actually use that index

originally reported in the forums:

http://forums.mysql.com/read.php?23,171148,171148#msg-171148

How to repeat:
--disable_warnings
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t3;
--enable_warnings

CREATE TABLE t1 (p POINT);
CREATE TABLE t2 (p POINT, INDEX(p));
CREATE TABLE t3 (p POINT, INDEX(p)) ENGINE=Innodb;

INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(1 2)'));
INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(1 2)'));
INSERT INTO t3 VALUES (POINTFROMTEXT('POINT(1 2)'));

-- no index, returns 1 as expected
SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)');

-- myisam with index, returns 1 as expected
-- EXPLAIN shows that the index is not used though
-- due to the "most rows covered anyway, so a scan is more effective" rule
SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');

-- returns 0 instead of 1, EXPLAIN shows the index is used
SELECT COUNT(*) FROM t3 WHERE p=POINTFROMTEXT('POINT(1 2)');

-- when forced to ignore the index the query works fine
SELECT COUNT(*) FROM t3 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)');

-- adding another row to the MyISAM table so that
-- the "most rows covered" rule doesn't kick in anymore
-- now EXPLAIN shows the index used on the MyISAM table
-- and we're getting the wrong result again
INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(1 2)'));
SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');

expected result: all queries returning a count of 1

actual result: whereever the optimizer choses to use the index the result becomes 0

Suggested fix:
fix whatever is wrong with using the index here (preferred),
or make the optimizer ignore the index in all cases,
or just don't allow regular indexes on geometry columns to be created at all
[5 Sep 2007 10:48] Hartmut Holzgraefe
mysqltest test case

Attachment: bug30825.tgz (application/x-gtar, text), 1.21 KiB.

[10 Oct 2007 13:26] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/35285

ChangeSet@1.2537, 2007-10-10 16:26:02+03:00, gkodinov@magare.gmz +14 -0
  Bug #30825: Problems when putting a non-spatial index on a GIS column
   Fixed the usage of spatial data (and Point in specific) with 
   non-spatial indexes.
   Several problems :
     - The length of the Point class was not updated to include the 
       spatial reference system identifier. Fixed by increasing with 4 
       bytes.
     - The storage length of the spatial columns was not accounting for
       the length that is prepended to it. Fixed by treating the 
       spatial data columns as blobs (and thus increasing the storage
       length)
     - When creating the key image for comparison in index read wrong
       key image was created (the one needed for and r-tree search,
       not the one for b-tree/other search). Fixed by treating the
       spatial data columns as blobs (and creating the correct kind of
       image based on the index type).
[29 Oct 2007 8:44] Bugs System
Pushed into 5.0.52
[29 Oct 2007 8:47] Bugs System
Pushed into 5.1.23-beta
[29 Oct 2007 8:51] Bugs System
Pushed into 6.0.4-alpha
[5 Nov 2007 3:06] Paul DuBois
Noted in 5.0.52, 5.1.23, 6.0.4 changelogs.

For a spatial column with a regular (non-SPATIAL) index, queries
failed if the optimizer tried to use the index.