Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.4
Description
When a table with geometry field and spatial index is converted from MyISAM to InnoDB GIS function doesn't work as expected anymore:
- No records are being returned even the statement is true (the geometry falls within the given borders of another)
- When adding additional '=1' check - rows are returned but in that case no index is used (possible).
A small test case:
DROP TABLE IF EXISTS `objects`; |
|
|
CREATE TABLE `objects` ( |
`id` int(11) NOT NULL AUTO_INCREMENT, |
`bounds` geometry NOT NULL, |
PRIMARY KEY (`id`), |
SPATIAL KEY `bounds` (`bounds`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
|
|
INSERT INTO `objects` VALUES (1,0x000000000103000000010000001100000051653FC69612494000000000C0EA0840B101B67B2FE2484000000000C0220D40FDA9371F45D248400000000020481040B5270BD172D048400000000060E811409A033715FBCA484000000000E05812407C1B8214E8AB48400000000060D713404D0C25C71E9348400000000000CC1540259FDC1CBDAD48400000000070B21940B0226899F92949400000000060ED1B40D7B36C6FAF7849400000000080FB1A406582C73D5EC6494000000000205E18408FE35055B8E7494000000000F0CE13405631E16F75E449400000000080600F401A8FF47F8EC1494000000000402602407BFFEE6A2A4249400000000040340340EEA681117C25494000000000C0D1034051653FC69612494000000000C0EA0840); |
|
|
-- No rows are returned
|
SELECT id FROM objects WHERE ST_Contains(bounds, GeomFromText('POINT(50.83216 3.59565)')); |
EXPLAIN SELECT id FROM objects WHERE ST_Contains(bounds, GeomFromText('POINT(50.83216 3.59565)')); |
|
|
-- A row is returned but no index is being used
|
SELECT id FROM objects WHERE ST_Contains(bounds, GeomFromText('POINT(50.83216 3.59565)')) = 1; |
EXPLAIN SELECT id FROM objects WHERE ST_Contains(bounds, GeomFromText('POINT(50.83216 3.59565)')) = 1; |
|
|
|
|
-- ST_Within works fine and returns correct result and uses index
|
SELECT id FROM objects WHERE ST_Within(GeomFromText('POINT(50.83216 3.59565)'),bounds); |
|
|
|
|
-- MyISAM - everything works as expected
|
ALTER TABLE `objects` ENGINE=MyISAM; |
|
|
SELECT id FROM objects WHERE ST_Contains(bounds, GeomFromText('POINT(50.83216 3.59565)')); |
EXPLAIN SELECT id FROM objects WHERE ST_Contains(bounds, GeomFromText('POINT(50.83216 3.59565)')); |
|
|
SELECT id FROM objects WHERE ST_Contains(bounds, GeomFromText('POINT(50.83216 3.59565)')) = 1; |
EXPLAIN SELECT id FROM objects WHERE ST_Contains(bounds, GeomFromText('POINT(50.83216 3.59565)')) = 1; |
Also the oppposite funcion ST_Within works as expected.
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
-
- Closed
-