Bug #36481 InnoDB should ignore indexes when using spatial functions

• Submitted: 2 May 2008 21:40
• Reporter: Gary Pendergast
• Status: Verified
• Category: MySQL Server: InnoDB storage engine
• Severity: S4 (Feature request)
• Version: 5.0.58
• OS: Any
• Assigned to: Assigned Account
• CPU Architecture: Any

[2 May 2008 21:40] Gary Pendergast

Description:
InnoDB returns an error when it tries to use an index with a spatial function. This is a change to the behaviour of the fix for Bug #32125.

How to repeat:
DROP TABLE IF EXISTS test;
CREATE TABLE test (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
foo GEOMETRY NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO test(foo) VALUES(GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'));

SELECT id, AsText(foo) FROM test WHERE Contains(foo,GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'));
ALTER TABLE test ADD INDEX(foo(100));
SELECT id, AsText(foo) FROM test WHERE Contains(foo,GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'));
SELECT id, AsText(foo) FROM test IGNORE INDEX(foo) WHERE Contains(foo,GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'));

Suggested fix:
InnoDB should ignore the index for spatial functions.