MySQL Bugs: #93071: ST_ISVALID reports invalid GEOMETRY's as valid

Bug #93071	ST_ISVALID reports invalid GEOMETRY's as valid
Submitted:	4 Nov 2018 3:20	Modified:	14 Jan 2019 14:31
Reporter:	Mark Drake	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: GIS	Severity:	S2 (Serious)
Version:	8, 8.0.13	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	ST_ISVALID

Description:
ST_ISVALID should report Geometry with INVALID LAT or LONG as INVALID.

The Address table in the Sakila sample database contains 5 rows with invalid LAT/LNG values. As can be seen below ST_ISVALID thinks are rows are valid.

mysql> select count(*) from sakila.address where st_isValid(location);
--------------
select count(*) from sakila.address where st_isValid(location)
--------------

+----------+
| count(*) |
+----------+
|      603 |
+----------+
1 row in set (0.02 sec)

However when you convert the values in GEOJSON and then attempt to convert them back into GEOMETRY data the invalid LAT and LNG values cause the operation to fail.

mysql> select st_geomfromgeojson(st_asGeoJSON(location)) from sakila.address;
--------------
select st_geomfromgeojson(st_asGeoJSON(location)) from sakila.address
--------------

ERROR 3616 (22S02): Longitude 766456911.000000 is out of range in function st_geomfromgeojson. It must be within (-180.000000, 180.000000].
mysql>

ST_ISVALUD should have flagged the rows as invalid. As an aside ST_ASGEOJSON should probably also have reported the rows as invalid as ST_GEOFROMGEOJSON cannot process the documents generated by ST_ASGEOJSON.

How to repeat:
Install the SAKILA sample database and run the commands above.

Hello Mark Drake,

Thank you for the report.

regards,
Umesh

The points in question are in SRID 0, which is an infinite abstract plane. Hence, any coordinate values are valid. If the points are cast to a latitude-longitude system, e.g., WGS 84, MySQL correctly raises an error:

mysql> SELECT COUNT(*) FROM sakila.address WHERE ST_ISVALID(ST_SRID(location, 4326));
ERROR 3731 (22S02): A parameter of function st_srid contains a geometry with longitude 766456911.000000, which is out of range. It must be within (-180.000000, 180.000000].

I'm closing this as not a bug