| Bug #72781 | Standard index breakes spatial one | ||
|---|---|---|---|
| Submitted: | 28 May 2014 12:01 | Modified: | 11 May 2015 12:24 |
| Reporter: | Adam Charzewski | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: GIS | Severity: | S3 (Non-critical) |
| Version: | 5.1.70/5.5 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | spatial index | ||
[28 May 2014 16:26]
MySQL Verification Team
C:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --debug-info --prompt="mysql 5.1 > "
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.74-Win X64 Source distribution
Copyright (c) 2000, 2013, 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 5.1 > CREATE DATABASE d21;
Query OK, 1 row affected (0.03 sec)
mysql 5.1 > USE d21
Database changed
mysql 5.1 > CREATE TABLE `g` (
-> `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
-> `P` point NOT NULL,
-> PRIMARY KEY (`ID`),
-> UNIQUE KEY `Unique_P` (`P`(25)),
-> SPATIAL KEY `Index_P` (`P`)
-> ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.06 sec)
mysql 5.1 > INSERT INTO g (P) VALUES (GeometryFromText('POINT(1 1)'));
Query OK, 1 row affected (0.00 sec)
mysql 5.1 > INSERT INTO g (P) VALUES (GeometryFromText('POINT(2 2)'));
Query OK, 1 row affected (0.00 sec)
mysql 5.1 > SELECT cast(AsText(P) as char(200)),ID FROM g WHERE within(P,GeometryFromText('POLYGON((1 1, 1 2, 2 2, 2 1, 1 1))'));
Empty set (0.01 sec)
mysql 5.1 > ALTER TABLE `g` DROP INDEX `Unique_P`;
Query OK, 2 rows affected (0.16 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql 5.1 > SELECT cast(AsText(P) as char(200)),ID FROM g WHERE within(P,GeometryFromText('POLYGON((1 1, 1 2, 2 2, 2 1, 1 1))'));
+------------------------------+----+
| cast(AsText(P) as char(200)) | ID |
+------------------------------+----+
| POINT(1 1) | 1 |
| POINT(2 2) | 2 |
+------------------------------+----+
2 rows in set (0.01 sec)
[28 May 2014 16:30]
MySQL Verification Team
Thank you for the bug report. I suggest you to move to 5.6:
C:\dbs>c:\dbs\5.5\bin\mysql -uroot --port=3550 --prompt="mysql 5.5 > "
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.38 Source distribution
Copyright (c) 2000, 2014, 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 5.5 > CREATE DATABASE d21;
Query OK, 1 row affected (0.01 sec)
mysql 5.5 > USE d21
Database changed
mysql 5.5 > CREATE TABLE `g` (
-> `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
-> `P` point NOT NULL,
-> PRIMARY KEY (`ID`),
-> UNIQUE KEY `Unique_P` (`P`(25)),
-> SPATIAL KEY `Index_P` (`P`)
-> ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.09 sec)
mysql 5.5 > INSERT INTO g (P) VALUES (GeometryFromText('POINT(1 1)'));
Query OK, 1 row affected (0.02 sec)
mysql 5.5 > INSERT INTO g (P) VALUES (GeometryFromText('POINT(2 2)'));
Query OK, 1 row affected (0.00 sec)
mysql 5.5 > SELECT cast(AsText(P) as char(200)),ID FROM g WHERE within(P,GeometryFromText('POLYGON((1 1, 1 2, 2 2, 2 1, 1 1))'));
Empty set (0.00 sec)
mysql 5.5 > exit
Bye
C:\dbs>56
C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 --debug-info --prompt="mysql 5.6 > "
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.19 Source distribution
Copyright (c) 2000, 2014, 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 5.6 > CREATE DATABASE d22;
Query OK, 1 row affected (0.05 sec)
mysql 5.6 > USE d22
Database changed
mysql 5.6 > CREATE TABLE `g` (
-> `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
-> `P` point NOT NULL,
-> PRIMARY KEY (`ID`),
-> UNIQUE KEY `Unique_P` (`P`(25)),
-> SPATIAL KEY `Index_P` (`P`)
-> ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.06 sec)
mysql 5.6 > INSERT INTO g (P) VALUES (GeometryFromText('POINT(1 1)'));
Query OK, 1 row affected (0.00 sec)
mysql 5.6 > INSERT INTO g (P) VALUES (GeometryFromText('POINT(2 2)'));
Query OK, 1 row affected (0.00 sec)
mysql 5.6 > SELECT cast(AsText(P) as char(200)),ID FROM g WHERE within(P,GeometryFromText('POLYGON((1 1, 1 2, 2 2, 2 1, 1 1))'));
+------------------------------+----+
| cast(AsText(P) as char(200)) | ID |
+------------------------------+----+
| POINT(1 1) | 1 |
| POINT(2 2) | 2 |
+------------------------------+----+
2 rows in set (0.00 sec)
[11 May 2015 12:24]
Norvald Ryeng
Posted by developer: Hi, I'm closing this bug since it has been fixed in 5.7. The correct behavior is to not return any rows, since both points are on the boundary of the polygon. In order for a point to be within a polygon, it has to be in the interior of the polygon, not just on the boundary.
Description: Query with condition on spatial column does not return rows when used on table with unique (or index) and spatial index SELECT cast(AsText(P) as char(200)),ID FROM g WHERE within(P,GeometryFromText('POLYGON((1 1, 1 2, 2 2, 2 1, 1 1))')); How to repeat: CREATE TABLE `g` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `P` point NOT NULL, PRIMARY KEY (`ID`), UNIQUE KEY `Unique_P` (`P`(25)), SPATIAL KEY `Index_P` (`P`) ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 INSERT INTO g (P) VALUES (GeometryFromText('POINT(1 1)')); INSERT INTO g (P) VALUES (GeometryFromText('POINT(2 2)')); Following query does not return any row: SELECT cast(AsText(P) as char(200)),ID FROM g WHERE within(P,GeometryFromText('POLYGON((1 1, 1 2, 2 2, 2 1, 1 1))')); Explain sugests that query uses Unique index not the spatial one. Suggested fix: Work around: Drop Unique_P index ALTER TABLE `g` DROP INDEX `Unique_P`; After removing unique index following query returns two rows. SELECT cast(AsText(P) as char(200)),ID FROM g WHERE within(P,GeometryFromText('POLYGON((1 1, 1 2, 2 2, 2 1, 1 1))'));