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:
None 
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
Triage: Needs Triage: D2 (Serious)

[3 Mar 2012 11:45] Achilleas Kotsis
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)
[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.