[MDEV-15497] Wrong empty value in a GEOMETRY column on LOAD DATA Created: 2018-03-07  Updated: 2019-02-23  Resolved: 2018-03-07

Status: Closed
Project: MariaDB Server
Component/s: Data types, GIS
Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3
Fix Version/s: 10.3.6

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-14628 Wrong autoinc value assigned by LOAD ... Closed
relates to MDEV-15479 Empty string is erroneously allowed a... Open

 Description   

I go to MariaDB data directory and create a file consisting of a single digit 1 in the database test:

echo 1 >test/data.txt

Now I load this file into a table with two columns, with the second column being GEOMETRY NOT NULL:

SET sql_mode='';
 
DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (id INT, a GEOMETRY NOT NULL);
 
LOAD DATA INFILE 'data.txt' INTO TABLE t1;


ERROR 1263 (22004): Column set to default value; NULL supplied to NOT NULL column 'a' at row 1

Notice, the file ends unexpectedly and the GEOMETRY column correctly reports the error that it cannot be set to a valid value.

Now I run the same script but add a FIELD TERMINATED BY '' clause:

SET sql_mode='';
 
DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (id INT, a GEOMETRY NOT NULL);
 
LOAD DATA INFILE 'data.txt' INTO TABLE t1 FIELDS TERMINATED BY '';


Query OK, 1 row affected, 1 warning (0.00 sec)
 
Records: 1  Deleted: 0  Skipped: 0  Warnings: 1

Hmm, it inserted a record. Let's check values:

SELECT id, HEX(a) FROM t1;


+------+--------+
 
| id   | HEX(a) |
 
+------+--------+
 
|    1 |        |
 
+------+--------+

Notice, the GEOMETRY column was assigned to an empty string value. This is not a valid GEOMETRY value. It should report the same error with the first script.

Now I test the same scripts, with for nullable columns:

SET sql_mode='';
 
DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (id INT, a GEOMETRY);
 
LOAD DATA INFILE 'data.txt' INTO TABLE t1;


Query OK, 1 row affected, 1 warning (0.00 sec)
 
Records: 1  Deleted: 0  Skipped: 0  Warnings: 1

Notice, it inserted the record. Let's check values:

SELECT id,HEX(a) FROM t1;


+------+--------+
 
| id   | HEX(a) |
 
+------+--------+
 
|    1 | NULL   |
 
+------+--------+

It inserted NULL into the GEOMETRY column. Looks fine.

Now do the same with FIELDS TERMINATED BY '':

SET sql_mode='';
 
DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (id INT, a GEOMETRY);
 
LOAD DATA INFILE 'data.txt' INTO TABLE t1 FIELDS TERMINATED BY '';


Query OK, 1 row affected, 1 warning (0.00 sec)
 
Records: 1  Deleted: 0  Skipped: 0  Warnings: 1

It inserted a record, let's check values:

SELECT id,HEX(a) FROM t1;


+------+--------+
 
| id   | HEX(a) |
 
+------+--------+
 
|    1 |        |
 
+------+--------+

Oops. It erroneously inserted an empty string again. It should insert NULL, like in the previous script.

 Comments   
Comment by Alexander Barkov [ 2018-03-07 ]

A similar problem is repeatable with the CHAR data type (again, when the column b does not have data):

SET sql_mode='';
 
CREATE OR REPLACE TABLE t1 (a CHAR(1), b CHAR(1));
 
LOAD DATA INFILE 'data.txt' INTO TABLE t1;
 
SHOW WARNINGS;
 
SELECT * FROM t1;


+------+------+
 
| a    | b    |
 
+------+------+
 
| 1    | NULL |
 
+------+------+


SET sql_mode='';
 
CREATE OR REPLACE TABLE t1 (a CHAR(1), b CHAR(1));
 
LOAD DATA INFILE 'data.txt' INTO TABLE t1 FIELDS TERMINATED BY '';
 
SHOW WARNINGS;
 
SELECT * FROM t1;


+------+------+
 
| a    | b    |
 
+------+------+
 
| 1    |      |
 
+------+------+

Notice, without FIELDS TERMINATED BY, column b gets assigned to NULL. With FIELDS TERMINATED BY it gets assigned to empty string.

But this seems to be by design.

Comment by Alexander Barkov [ 2019-02-23 ]

Backported to 10.2.23, as a part of MDEV-18045.

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