Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5, 10.0, 10.1, 10.2, 10.3
-
None
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.
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
-
- Open
-