MySQL Bugs: #79407: ST_within and ST_contains return inconsistent results

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

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 |

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 >

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?

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.