Bug #75829 ST_Centroid produces incorrect results with MultiPolygon
Submitted: 9 Feb 2015 19:36 Modified: 21 May 2015 14:31
Reporter: Bryan Blakey Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: GIS Severity:S2 (Serious)
Version: 5.6.23 OS:Any
Assigned to: CPU Architecture:Any

[9 Feb 2015 19:36] Bryan Blakey
Description:
We've been doing some testing preparing to upgrade from MySQL Server 5.5 to MySQL Server 5.6. During this testing we noticed that the output from Centroid() on 5.5 differs from that of ST_Centroid() on 5.6 when passing it a MultiPolygon geometry. 

Using the simple test case below, it seems to me that the output from 5.6 is incorrect.

How to repeat:
SET @mpoly = GeomFromText('MULTIPOLYGON(((0 0,0 2,2 2,2 0,0 0)),((0 4,0 6,2 6,2 4,0 4)),((4 0,4 2,6 2,6 0,4 0)),((4 4,4 6,6 6,6 4,4 4)))');

-- expected output: POINT(3 3)
-- actual output: POINT(4, 3.5)
SELECT AsText(ST_Centroid(@mpoly));
[9 Feb 2015 19:45] Bryan Blakey
Whoopsie. I accidentally added a comma between X and Y values in the test case "actual output" comment above. I am not getting the extra comma in the output from ST_Centroid (I'm just too used to typing coordinates that way in other languages). 

Actual output from running the test case on 5.6.23 is:

POINT(4 3.5)
[9 Feb 2015 23:22] Miguel Solorzano
Thank you for the bug report. 5.7 not affected.

mysql 5.6 > SHOW VARIABLES LIKE"%VERSION%";
+-------------------------+--------------------------------+
| Variable_name           | Value                          |
+-------------------------+--------------------------------+
| innodb_version          | 5.6.24                         |
| protocol_version        | 10                             |
| slave_type_conversions  |                                |
| version                 | 5.6.24                         |
| version_comment         | Source distribution 2015/02/09 |
| version_compile_machine | x86_64                         |
| version_compile_os      | Win64                          |
+-------------------------+--------------------------------+
7 rows in set (0.00 sec)

mysql 5.6 > SELECT AsText(ST_Centroid(@mpoly));
+-----------------------------+
| AsText(ST_Centroid(@mpoly)) |
+-----------------------------+
| POINT(4 3.5)                |
+-----------------------------+
1 row in set (0.00 sec)

mysql 5.7 > SHOW VARIABLES LIKE"%VERSION%";
+-------------------------+--------------------------------+
| Variable_name           | Value                          |
+-------------------------+--------------------------------+
| innodb_version          | 5.7.6                          |
| protocol_version        | 10                             |
| slave_type_conversions  |                                |
| version                 | 5.7.6-m16                      |
| version_comment         | Source distribution 2015/02/09 |
| version_compile_machine | x86_64                         |
| version_compile_os      | Win64                          |
+-------------------------+--------------------------------+
7 rows in set (0.00 sec)

mysql 5.7 > SELECT AsText(ST_Centroid(@mpoly));
+-----------------------------+
| AsText(ST_Centroid(@mpoly)) |
+-----------------------------+
| POINT(3 3)                  |
+-----------------------------+
1 row in set, 1 warning (0.00 sec)

mysql 5.7 > show warnings;
+---------+------+----------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                      |
+---------+------+----------------------------------------------------------------------------------------------+
| Warning | 1287 | 'ASTEXT' is deprecated and will be removed in a future release. Please use ST_ASTEXT instead |
+---------+------+----------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

mysql 5.7 > SELECT ST_AsText(ST_Centroid(@mpoly));
+--------------------------------+
| ST_AsText(ST_Centroid(@mpoly)) |
+--------------------------------+
| POINT(3 3)                     |
+--------------------------------+
1 row in set (0.00 sec)
[21 May 2015 14:31] Paul Dubois
Noted in 5.7.8, 5.8.0 changelogs.

ST_Centroid() with a MultiPolygon argument could produce incorrect
results.
[21 May 2015 14:44] Paul Dubois
Issues were resolved by use of Boost.Geometry for GIS algorithms.