[MDEV-21401] 'INSERT ST_GeomFromText(GEOMETRYCOLLECTION ' cause ERROR 1048 (23000): Column SHAPE cannot be null Created: 2019-12-29  Updated: 2023-04-27

Status: Confirmed
Project: MariaDB Server
Component/s: GIS
Affects Version/s: 5.5, 10.1, 10.3.9, 10.4.11, 10.2, 10.3, 10.4
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Hiroshi Miura Assignee: Alexey Botchkov
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Docker image: mariadb:10.3.9
GDAL mysql driver


 Description   

When creating geospatial table, insert statement cause column is not null.

mysql> CREATE TABLE `ogr_mysql_28_15`  ( OGR_FID int(11) UNIQUE NOT NULL AUTO_INCREMENT, SHAPE GEOMETRY NOT NULL);
 
Query OK, 0 rows affected (0.05 sec)
 
 
 
mysql> INSERT INTO `ogr_mysql_28_15` (`SHAPE` ) VALUES (ST_GeomFromText('GEOMETRYCOLLECTION (POINT (4 6),GEOMETRYCOLLECTION (POINT (4 6),LINESTRING (4 6,7 10)))',2) );
 
ERROR 1048 (23000): Column 'SHAPE' cannot be null
 
mysql> describe ogr_mysql_28_15;
 
+---------+----------+------+-----+---------+----------------+
 
| Field   | Type     | Null | Key | Default | Extra          |
 
+---------+----------+------+-----+---------+----------------+
 
| OGR_FID | int(11)  | NO   | PRI | NULL    | auto_increment |
 
| SHAPE   | geometry | NO   | MUL | NULL    |                |
 
+---------+----------+------+-----+---------+----------------+
 
2 rows in set (0.00 sec)

This is an issue in feature test case of GDAL project, which MariaDB fails.
https://github.com/OSGeo/gdal/pull/2137

 Comments   
Comment by Hiroshi Miura [ 2019-12-29 ]

Similar but different case such as
CREATE TABLE `ogr_mysql_28_14` ( OGR_FID INT UNIQUE NOT NULL AUTO_INCREMENT, SHAPE GEOMETRY NOT NULL)
INSERT INTO `ogr_mysql_28_14` (`SHAPE` ) VALUES (ST_GeomFromText('GEOMETRYCOLLECTION (POINT (4 6),LINESTRING (4 6,7 10))',2) )
works.

A same case with MySQL8.0.18 works.

CREATE TABLE `ogr_mysql_28_15` ( OGR_FID INT UNIQUE NOT NULL AUTO_INCREMENT, SHAPE GEOMETRY NOT NULL)
INSERT INTO `ogr_mysql_28_15` (`SHAPE` ) VALUES (ST_GeomFromText('GEOMETRYCOLLECTION (POINT (4 6),GEOMETRYCOLLECTION (POINT (4 6),LINESTRING (4 6,7 10)))',4326, 'axis-order=long-lat') )

Trying same query with MySQL 8 also fails in MariaDB.

mysql> INSERT INTO `ogr_mysql_28_15` (`SHAPE` ) VALUES (ST_GeomFromText('GEOMETRYCOLLECTION (POINT (4 6),GEOMETRYCOLLECTION (POINT (4 6),LINESTRING (4 6,7 10)))',4326, 'axis-order=long-lat') );
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'ST_GeomFromText'

In MySQL8, droping SRID such as
mysql> INSERT INTO `ogr_mysql_28_15` (`SHAPE` ) VALUES (ST_GeomFromText('GEOMETRYCOLLECTION (POINT (4 6),GEOMETRYCOLLECTION (POINT (4 6),LINESTRING (4 6,7 10)))') );
also works but it also fails with MariaDB.

Comment by Hiroshi Miura [ 2019-12-29 ]

I can reproduce it with mariadb 10.4.11, docker official image.

Comment by Elena Stepanova [ 2020-01-12 ]

Thanks for the report and test case.
The error on INSERT is an aftermath, it happens because 

MariaDB [test]> select ST_GeomFromText('GEOMETRYCOLLECTION (POINT (4 6),GEOMETRYCOLLECTION (POINT (4 6),LINESTRING (4 6,7 10)))',2);
 
+--------------------------------------------------------------------------------------------------------------+
 
| ST_GeomFromText('GEOMETRYCOLLECTION (POINT (4 6),GEOMETRYCOLLECTION (POINT (4 6),LINESTRING (4 6,7 10)))',2) |
 
+--------------------------------------------------------------------------------------------------------------+
 
| NULL                                                                                                         |
 
+--------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.000 sec)

on all of MariaDB 5.5-10.5.

MySQL 5.7 returns something that isn't null.

Comment by Hiroshi Miura [ 2020-03-05 ]

This issue is stale because it has been open 50 days with no activity after confirmation.
Any progress, or do you need anything more?

Generated at Thu Feb 08 09:06:51 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.