[MDEV-13123] POLYGON not accepted in MULTIPOLYGON column (works on MySQL 5.6.36-82.0) Created: 2017-06-19  Updated: 2017-11-03  Resolved: 2017-11-03

Status: Closed
Project: MariaDB Server
Component/s: GIS
Affects Version/s: 10.0.30, 10.1.21
Fix Version/s: 10.0.10

Type: Bug Priority: Major
Reporter: Mario DE WEERD Assignee: Alexey Botchkov
Resolution: Not a Bug Votes: 0
Labels: Compatibility, upstream
Environment:

Debian, Windows

Sprint: 10.1.29

 Description   

Mariadb does not accept a "POLYGON" in a "MULTIPOLYGON" table.
MySql does and the spatial search tree is also functionnal.

The MySql documentation says:
"The other collection types (MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, and GEOMETRYCOLLECTION) restrict collection members to those having a particular geometry type." I guess that "particular geometry type" is interpreted as "one or more points, one or more linestrings, one or more polygons, ...).

The following works with MySQL 5.6, not MariaDB. Compatibility is broken. This incompatibility is not listed (Incompatibilities 5.6).

CREATE TABLE `geo_area` (
 
  `geo_area_id` int(11) NOT NULL AUTO_INCREMENT,
 
  `area_type_id` int(11) DEFAULT NULL,
 
  `bb_lat_min` decimal(9,6) DEFAULT NULL,
 
  `bb_lat_max` decimal(9,6) DEFAULT NULL,
 
  `bb_lon_min` decimal(9,6) DEFAULT NULL,
 
  `bb_lon_max` decimal(9,6) DEFAULT NULL,
 
  `geo_area_nominal` multipolygon NOT NULL,
 
  `geo_area_inner` multipolygon NOT NULL,
 
  `geo_area_outer` multipolygon NOT NULL,
 
  `distance` decimal(4,1) NOT NULL,
 
  `displayname` varchar(45) DEFAULT NULL,
 
  `description` varchar(255) DEFAULT NULL,
 
  `date_upd` datetime DEFAULT NULL ,
 
  `address` varchar(255) DEFAULT NULL ,
 
  `visibility` int(4) DEFAULT NULL,
 
  PRIMARY KEY (`geo_area_id`),
 
  SPATIAL KEY `inner_geom` (`geo_area_inner`),
 
  SPATIAL KEY `outer_geom` (`geo_area_outer`),
 
  SPATIAL KEY `nominal_geom` (`geo_area_nominal`)
 
) ENGINE=MyISAM AUTO_INCREMENT=5911 DEFAULT CHARSET=utf8;
 
 
 
INSERT INTO `geo_area` VALUES (1,2,NULL,NULL,NULL,NULL,0x8C10000001030000000100000005000000A6E22F62179B004026DD8B3125824840BEE22F92309D00408798FED62D824840F9E22F42069F00406DE435F20B824840C3E22F52449C0040CAF60ACD01824840A6E22F62179B004026DD8B3125824840,0x8C10000001030000000100000005000000A6E22F62179B004026DD8B3125824840BEE22F92309D00408798FED62D824840F9E22F42069F00406DE435F20B824840C3E22F52449C0040CAF60ACD01824840A6E22F62179B004026DD8B3125824840,0x8C10000001030000000100000005000000A6E22F62179B004026DD8B3125824840BEE22F92309D00408798FED62D824840F9E22F42069F00406DE435F20B824840C3E22F52449C0040CAF60ACD01824840A6E22F62179B004026DD8B3125824840,10.0,'Dummy','',NULL,'Dummy',2) ;
 
 
 
INSERT INTO `geo_area` VALUES (2,2,NULL,NULL,NULL,NULL,
 
GeomFromText("POLYGON((2.0757281943191 49.016760056775,2.0767527981628 49.017023920359,2.0776486559738 49.015989567103,2.0763021870483 49.015679960588,2.0757281943191 49.016760056775))",4236),
 
GeomFromText("POLYGON((2.0757281943191 49.016760056775,2.0767527981628 49.017023920359,2.0776486559738 49.015989567103,2.0763021870483 49.015679960588,2.0757281943191 49.016760056775))",4236),
 
GeomFromText("POLYGON((2.0757281943191 49.016760056775,2.0767527981628 49.017023920359,2.0776486559738 49.015989567103,2.0763021870483 49.015679960588,2.0757281943191 49.016760056775))",4236),
 
10.0,'Dummy','',NULL,'Dummy',2);



 Comments   
Comment by Alice Sherepa [ 2017-06-19 ]

MariaDB 10.2.6 does not accept also a POINT in a MULTIPOINT column, and LINESTRING in a MULTILINESTRING. (e.g.ERROR 1366 Incorrect MULTILINESTRING value: 'LINESTRING' for column )

Just for the reference, mysql 5.7 does not accept it also and throws ERROR 1416 (22003): Cannot get geometry object from data you send to the GEOMETRY field

Comment by Mario DE WEERD [ 2017-06-19 ]

Thank you for checking this.
I got a copy of MySQL 5.7 and reproduced the issue there.

I have updated the test case (the next lines suppose that the table is created).

The code below works in my 5.6 versions, but not in 5.7 for the "POLYGON" while the "MULTIPOLYGON" works. It looks like I'm going to have a little bit of fun at upgrading the server (and the app).

SET @t='POLYGON((2.0757281943191 49.016760056775,2.0767527981628 49.017023920359,2.0776486559738 49.015989567103,2.0763021870483 49.015679960588,2.0757281943191 49.016760056775))';
 
SET @g=GeomFromText(@t,4236);
 
DELETE FROM `geo_area` WHERE geo_area_id=2;
 
INSERT INTO `geo_area` VALUES (2,2,NULL,NULL,NULL,NULL,@g,@g,@g,10.0,'Dummy','',NULL,'Dummy',2);
 
 
 
 
 
SET @t='MULTIPOLYGON(((2.0757281943191 49.016760056775,2.0767527981628 49.017023920359,2.0776486559738 49.015989567103,2.0763021870483 49.015679960588,2.0757281943191 49.016760056775)))';
 
SET @g=GeomFromText(@t,4236);
 
DELETE FROM `geo_area` WHERE geo_area_id=2;
 
INSERT INTO `geo_area` VALUES (2,2,NULL,NULL,NULL,NULL,@g,@g,@g,10.0,'Dummy','',NULL,'Dummy',2);

Comment by Elena Stepanova [ 2017-06-25 ]

le_top, thanks for the report and test case, and alice, for checking MySQL 5.7, it really changes things. If it was an intentional change in MySQL, then it is not an issue of compatibility, we can't be compatible with two mutually exclusive behaviors. However, I also couldn't find anything that would clearly state that POLYGON is not a MULTIPOLYGON etc., so I'm assigning it to holyfoot to clarify and maybe make a note about it in the documentation (or fix it, if it turns out to be an oversight after all).

Comment by Alexey Botchkov [ 2017-11-03 ]

Mysql 5.6 just doesn't control geometry types at all. For instance POINT can be inserted there into MYLTIPOLYGON column, not just POLYGON.
MySQL 5.7 and MariaDB 10 have the type validation enabled so one only can insert MULTYPOLYGON into the MULTIPOLYGON column.
Well we can think of automatic conversion here, but that would be a separate task.
As an obvious workaround i'd recommend use just GEOMETRY fieldtype instead of the MULTIPOLYGON. Should work just as the MULTIPOLYGON in MySQL5.6.

Generated at Thu Feb 08 08:03:04 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.