MySQL Bugs: #30825: Problems when putting a non-spatial index on a GIS column

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

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

mysqltest test case

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

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).

Pushed into 5.0.52

Pushed into 5.1.23-beta

Pushed into 6.0.4-alpha

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.