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.
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.