MySQL Bugs: #80123: Invalid MULTIPOLYGON geometry permitted to be added in geometry columns

Bug #80123	Invalid MULTIPOLYGON geometry permitted to be added in geometry columns
Submitted:	22 Jan 2016 23:15	Modified:	29 Feb 2016 18:55
Reporter:	Nikolaos Priggouris	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: GIS	Severity:	S2 (Serious)
Version:	5.7.9, 5.7.10	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Trying to add a 'MULTIPOLYGON(((0 0,0 0,0 0,0 0)))' to a geometry column succeeds althought GIS data seems to be invalid since spatial operation involving this entry fail either silently or with the following error:
 Error Code: 3037. Invalid GIS data provided to function st_within.

How to repeat:
-- Example script to create needed data to replicate the problem:

CREATE TABLE `test` (
  `FID` bigint(20) NOT NULL AUTO_INCREMENT,
  `KAEK` varchar(12),
  `the_geom` multipolygon NOT NULL,
  PRIMARY KEY (`FID`),
  UNIQUE KEY `KAEK` (`KAEK`),
  SPATIAL KEY `test_the_geom_idx` (`the_geom`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `test2` (
  `FID` bigint(20) NOT NULL AUTO_INCREMENT,
  `KAEK` varchar(12),
  `the_geom` multipolygon NOT NULL,
  PRIMARY KEY (`FID`),
  UNIQUE KEY `KAEK` (`KAEK`),
  SPATIAL KEY `test2_the_geom_idx` (`the_geom`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test`(`FID`, `KAEK`,`the_geom`) VALUES (1, '100', ST_MPolyFromText('MULTIPOLYGON(((0 0,0 0,0 0,0 0)))',2100)); 
INSERT INTO `test`(`FID`, `KAEK`,`the_geom`) VALUES (2, '200', ST_MPolyFromText('MULTIPOLYGON(((0 0,1 0,1 1,0 1,0 0)))',2100)); 

INSERT INTO `test2`(`FID`, `KAEK`,`the_geom`) VALUES (1, '200', ST_MPolyFromText('MULTIPOLYGON(((0 0,1 0,1 1,0 1,0 0)))',2100)); 
 
-- Now execute spatial operations 

--(this one fails silently with OK)
select t1.kaek  from test2 t2, test t1 where ST_Contains(t2.the_geom,t1.the_geom)

-- (this one throws an Error Code: 3037. Invalid GIS data provided to function st_within.)
select t1.kaek  from test2 t2, test t1 where ST_Contains(t2.the_geom,t1.the_geom)
union
select t1.kaek  from test2 t2, test t1 where st_overlaps(t2.the_geom,t1.the_geom)  
 

Suggested fix:
invalid geometries should generally not be permitted and throw an error upon insert or update.
Especially MULTIPOLYGON(((0 0,0 0,0 0,0 0))) seems to cause a big problem. If it is considered invalid it should not be permitted otherwise it should be accepted by the spatial functions.

Hello Nikolaos,

Thank you for the report.

Thanks,
Umesh

Bug #80262 marked as duplicate of this

Posted by developer:
 
Checking geometry validity is an expensive operation, so MySQL does only minimal verification of geometries on INSERT. GIS functions are only defined for valid input (https://dev.mysql.com/doc/refman/5.7/en/spatial-function-argument-handling.html), but checking validity is expensive, so no explicit check is made. However, GIS functions are allowed to flag an error if invalid geometries are detected.

This is intended behavior and in line with other DBMSs.

To avoid inserting and computing on invalid data, use ST_IsValid and ST_Validate:

https://dev.mysql.com/doc/refman/5.7/en/spatial-convenience-functions.html#function_st-isv...
https://dev.mysql.com/doc/refman/5.7/en/spatial-convenience-functions.html#function_st-val...

I'm closing this report as not a bug since this is intended and documented behavior.

Regards,

Norvald H. Ryeng

Ok however kee in mind that the functions you mention ST_Valid() & ST_Validate() do apply only for cartesian coordinates (SRID=0) which is quite limiting since cartesian coordinates are rearely used in real world scenarios