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

[4 Nov 2018 3:20] Mark Drake
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].

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.
[7 Nov 2018 5:05] Umesh Shastry
Hello Mark Drake,

Thank you for the report.

[14 Jan 14:31] Norvald Ryeng
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