Bug #88503 ST_X and ST_Y not working right in 8.0.3-rc-log
Submitted: 15 Nov 2017 15:52 Modified: 16 Apr 17:05
Reporter: Joseph Dornisch Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: GIS Severity:S1 (Critical)
Version:8.0.3-rc-log OS:Microsoft Windows (10)
Assigned to: CPU Architecture:Any
Tags: spatial server ST_X ST_Y

[15 Nov 2017 15:52] Joseph Dornisch
Description:
I was working with Version 8 on my development machine where I had odd errors on my development machine. I traced the problem to ST_X and ST_Y not working as expected. On production we use 5.7 and have been using mysql for years. I should note this change is also inconsistent with how Postgres version 10.0 handles ST_X and ST_Y which we use on some machines.

I created a simple script that I executed on both 8.0.3rc and 5.7 and here are the results:
In 8.0.3.rc:
mysql> show variables like 'version';
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| version       | 8.0.3-rc-log |
+---------------+--------------+
1 row in set (0.01 sec)
mysql> create table testpoint(pos point NOT NULL) ENGINE=InnoDB;
Query OK, 0 rows affected (0.04 sec)
mysql> insert into testpoint (pos) values (ST_GeomFromText('POINT( 1 2)'));
Query OK, 1 row affected (0.01 sec)
mysql> insert into testpoint (pos) values (ST_GeomFromText('POINT( 1 2)', 4326));
Query OK, 1 row affected (0.00 sec)
mysql> select st_x(pos), st_y(pos), st_AsText(pos) from testpoint;
+-----------+-----------+----------------+
| st_x(pos) | st_y(pos) | st_AsText(pos) |
+-----------+-----------+----------------+
|         1 |         2 | POINT(1 2)     |
|         2 |         1 | POINT(1 2)     |
+-----------+-----------+----------------+
2 rows in set (0.00 sec)

Now here is the exact same set of commands and their responses in 5.7:
mysql> show variables like 'version';
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| version       | 5.7.20-log |
+---------------+------------+
1 row in set (0.01 sec)

mysql> create table testpoint(pos point NOT NULL) ENGINE=InnoDB;
Query OK, 0 rows affected (0.04 sec)
mysql> insert into testpoint (pos) values (ST_GeomFromText('POINT( 1 2)'));
Query OK, 1 row affected (0.01 sec)
mysql> insert into testpoint (pos) values (ST_GeomFromText('POINT( 1 2)', 4326));
Query OK, 1 row affected (0.00 sec)
mysql> select st_x(pos), st_y(pos), st_AsText(pos) from testpoint;
+-----------+-----------+----------------+
| st_x(pos) | st_y(pos) | st_AsText(pos) |
+-----------+-----------+----------------+
|         1 |         2 | POINT(1 2)     |
|         1 |         2 | POINT(1 2)     |
+-----------+-----------+----------------+
2 rows in set (0.00 sec)

Notice, that when the SRID is used for then the point returned by ST_X and ST_Y is swapped. This will break a lot of code out there by any project that is using spatial products.

How to repeat:
Install the 8.0.3rc release of MySQL server (or any of the 8.0.x.rc releases so far as far I can tell) and run the following script:
show variables like 'version';
create table testpoint(pos point NOT NULL) ENGINE=InnoDB; 
insert into testpoint (pos) values (ST_GeomFromText('POINT( 1 2)'));
insert into testpoint (pos) values (ST_GeomFromText('POINT( 1 2)', 4326));
select st_x(pos), st_y(pos), st_AsText(pos) from testpoint;

Suggested fix:
ST_X and ST_Y should always report the position as they do in the command without the SRID and as they do in MySql version 5.7.
[15 Nov 2017 21:07] Miguel Solorzano
Thank you for the bug report. Verified as described.
[16 Apr 17:05] Paul Dubois
Posted by developer:
 
Fixed in 8.0.12.

The ST_X() and ST_Y() functions now interpret their Point argument
according to its spatial reference system definition (SRS):

If the Point argument has a geographic SRS, ST_X() returns the
coordinate value of the axis that appears first in the SRS
definition, and ST_Y() returns the coordinate value of the axis that
appears second in the SRS definition.

If ST_X() and ST_Y() are used to set the latitude or longitude of a
Point argument that has a geographic SRS, the latitude or longitude
value must be in the proper range for the SRS, or an error occurs.