| Bug #64533 | Query using spatial index returns empty result | ||
|---|---|---|---|
| Submitted: | 3 Mar 2012 11:45 | Modified: | 25 Jul 2012 19:35 |
| Reporter: | Achilleas Kotsis | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: GIS | Severity: | S2 (Serious) |
| Version: | 5.6.4-m7, 5.6.5-m8 | OS: | Any (x86_64) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | gis, spatial index | ||
[3 Mar 2012 12:27]
Valeriy Kravchuk
Thank you for the bug report. Verified on Mac OS X also:
macbook-pro:trunk openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.5-m8-debug Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> drop table test;
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE `test` (
-> `region_id` int(10) NOT NULL AUTO_INCREMENT,
-> `area` polygon NOT NULL,
-> PRIMARY KEY (`region_id`),
-> SPATIAL KEY `area` (`area`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
Query OK, 0 rows affected (0.06 sec)
mysql> INSERT INTO `test` (`region_id` ,`area`)
-> VALUES (NULL, GeomFromText('POLYGON((37.961911 23.731155,37.965159 23.752441,37.977068 23.753128,37.977068 23.728065,37.961911 23.731155))')),
-> (NULL, GeomFromText('POLYGON((38.034195 23.840504,38.016413 23.845568,38.019186 23.873892,38.050524 23.875523,38.034195 23.840504))'));
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT `region_id` FROM `test` IGNORE INDEX (`area`) WHERE ST_Contains(`area`,
-> GeomFromText('Point(38.0248492 23.8512726)'));
+-----------+
| region_id |
+-----------+
| 2 |
+-----------+
1 row in set (0.01 sec)
mysql> SELECT `region_id` FROM `test` WHERE ST_Contains(`area`,
-> GeomFromText('Point(38.0248492 23.8512726)'));
Empty set (0.01 sec)
[25 Jul 2012 19:35]
Paul DuBois
Noted in 5.6.7, 5.7.0 changelogs. Queries that used the ST_Contains and Within() functions yielded incorrect results when argument columns had a spatial index.
Description: A query returns empty results when a spatial index is used, and the correct results when the index is ignored. So, spatial indexes are unusable. MySQL 5.6.4-m7 compiled from source on Debian squeeze x86_64 How to repeat: mysql> CREATE TABLE `test` ( -> `region_id` int(10) NOT NULL AUTO_INCREMENT, -> `area` polygon NOT NULL, -> PRIMARY KEY (`region_id`), -> SPATIAL KEY `area` (`area`) -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO `test` ( -> `region_id` , -> `area` -> ) -> VALUES ( -> NULL , GeomFromText( 'POLYGON((37.961911 23.731155,37.965159 23.752441,37.977068 23.753128,37.977068 23.728065,37.961911 23.731155))' ) -> ), ( -> NULL , GeomFromText( 'POLYGON((38.034195 23.840504,38.016413 23.845568,38.019186 23.873892,38.050524 23.875523,38.034195 23.840504))' ) -> ); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT `region_id` FROM `test` IGNORE INDEX (`area`) WHERE ST_Contains(`area`, GeomFromText('Point(38.0248492 23.8512726)')); +-----------+ | region_id | +-----------+ | 2 | +-----------+ 1 row in set (0.00 sec) mysql> SELECT `region_id` FROM `test` WHERE ST_Contains(`area`, GeomFromText('Point(38.0248492 23.8512726)')); Empty set (0.00 sec)