Bug #79407 ST_within and ST_contains return inconsistent results
Submitted: 25 Nov 2015 10:39 Modified: 25 Nov 2015 12:03
Reporter: Sebastian Hönel Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: GIS Severity:S2 (Serious)
Version:5.7.9 GA OS:Any
Assigned to: CPU Architecture:Any
Tags: gis, spatial, ST_Contains, st_within

[25 Nov 2015 10:39] Sebastian Hönel
Description:
ST_Contains and ST_Within are supposed to return the same set of results when called like this (GEOMETRY is a polygon):

ST_Contains(GEOMETRY, POINT);
ST_WITHIN(POINT, GEOMETRY);

However, on MySQL 5.7.9 using InnoDB or MyISAM, ST_Contains returns incomplete results.
This bug was first described here: http://stackoverflow.com/questions/33868449/mysql-5-7-st-contains-not-returning-all-matche...

How to repeat:
Create the following table (or skip to next point):

CREATE TABLE `test` (
 `area_id` bigint(20) NOT NULL AUTO_INCREMENT,
 `boundary` geometry NOT NULL,
 PRIMARY KEY (`area_id`),
 SPATIAL KEY `boundary` (`boundary`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

---

Download this test file which contains only 4 rows: https://drive.google.com/open?id=0B5_uvSaA5pYmQTAzU1prSDAxQVE and insert it.

---

Run the following query using ST_CONTAINS:

set @p = st_GeomFromText('POINT(14.822664 56.858916)');
select area_id, st_contains(boundary, @p) from test;

The result of this query is (notice that ST_Contains for ID 26621 is 0 which is an error!):

| area_id | st_contains(boundary, @p) |
| ------- | ------------------------- |
| 209     |                         1 |
| 299     |                         1 |
| 2417    |                         1 |
| 26621   |                         0 |

What acknowledges this bug is this query for the spatial test of ID 26621 (which was 0 above):

set @boundary = (select boundary from test where area_id = 26621);
select st_contains(@boundary, @p);

| st_contains(@boundary, @p) |
| -------------------------- |
|                          1 |

---

This might be an inconsistency between ST_CONTAINS and ST_WITHIN, because the latter returns the correct results:

set @p = st_GeomFromText('POINT(14.822664 56.858916)');
select area_id, st_within(@p, boundary) from test;

| area_id | st_within(@p, boundary) |
| ------- | ----------------------- |
| 209     |                       1 |
| 299     |                       1 |
| 2417    |                       1 |
| 26621   |                       1 |
[25 Nov 2015 11:54] MySQL Verification Team
Thank you for the bug report. I was able to repeat with 5.7.9 released version but not anymore with source server:

mysql> set @p = st_GeomFromText('POINT(14.822664 56.858916)');
Query OK, 0 rows affected (0.00 sec)

mysql> select area_id, st_contains(boundary, @p) from test;
+---------+---------------------------+
| area_id | st_contains(boundary, @p) |
+---------+---------------------------+
|     209 |                         1 |
|     299 |                         1 |
|    2417 |                         1 |
|   26621 |                         0 |
+---------+---------------------------+
4 rows in set (0.01 sec)

mysql> show variables like "%version%";
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.9                        |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.7.9                        |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Win64                        |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)

mysql 5.7 > set @p = st_GeomFromText('POINT(14.822664 56.858916)');
Query OK, 0 rows affected (0.00 sec)

mysql 5.7 > select area_id, st_contains(boundary, @p) from test;
+---------+---------------------------+
| area_id | st_contains(boundary, @p) |
+---------+---------------------------+
|     209 |                         1 |
|     299 |                         1 |
|    2417 |                         1 |
|   26621 |                         1 |
+---------+---------------------------+
4 rows in set (0.01 sec)

mysql 5.7 > show variables like "%version%";
+-------------------------+---------------------------------------+
| Variable_name           | Value                                 |
+-------------------------+---------------------------------------+
| innodb_version          | 5.7.10                                |
| protocol_version        | 10                                    |
| slave_type_conversions  |                                       |
| version                 | 5.7.10                                |
| version_comment         | Source distribution PULL: 2015-NOV-07 |
| version_compile_machine | x86_64                                |
| version_compile_os      | Win64                                 |
+-------------------------+---------------------------------------+
7 rows in set (0.00 sec)

mysql 5.7 >
[25 Nov 2015 12:03] Sebastian Hönel
Thanks for checking/verifying the described behavior. So I guess this is already fixed in the next version (5.7.10+) which will then (when?) be released, right?
For clarification, you set this bug to "Can't repeat" because it cannot be repeated in a version newer than 5.7.9?
[25 Nov 2015 12:07] MySQL Verification Team
Sorry but I don't know when will be released. Yes the can't repeat is with current source build server with a note it was repeatable with reported version. Thanks.