[MDEV-15497] Wrong empty value in a GEOMETRY column on LOAD DATA - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
Fix Version/s: 10.3.6
Component/s: Data types, GIS
Labels:
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

MDEV-14628 Wrong autoinc value assigned by LOAD XML in the NO_AUTO_VALUE_ON_ZERO mode

Closed

MDEV-15479 Empty string is erroneously allowed as a GEOMETRY column value

Stalled

Activity

Ascending order - Click to sort in descending order

Alexander Barkov created issue - 2018-03-07 02:36

Alexander Barkov made changes - 2018-03-07 02:37

Field	Original Value	New Value
Rank		Ranked higher

Alexander Barkov made changes - 2018-03-07 02:37

Link

This issue relates to ~~MDEV-14628~~ [ ~~MDEV-14628~~ ]

Alexander Barkov made changes - 2018-03-07 02:38

Link

This issue relates to MDEV-15479 [ MDEV-15479 ]

Alexander Barkov made changes - 2018-03-07 12:08

Description

I go to MariaDB data directory and create a file consisting of a single digit {{1}} in the database {{test}}:
{{noformat}}
echo 1 >test/data.txt
{{noformat}}

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

{code:sql}
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;
{code}
{noformat}
ERROR 1263 (22004): Column set to default value; NULL supplied to NOT NULL column 'a' at row 1
{noformat}

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:

{code:sql}
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 '';
{code}
{noformat}
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 1
{noformat}

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

{code:sql}
SELECT id, HEX(a) FROM t1;
{code}
{noformat}
+------+--------+
| id | HEX(a) |
+------+--------+
| 1 | |
+------+--------+
{noformat}

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:

{code:sql}
SET sql_mode='';
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (id INT, a GEOMETRY);
LOAD DATA INFILE 'data.txt' INTO TABLE t1;
{code}
{noformat}
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 1
{noformat}
Notice, it inserted the record. Let's check values:
{code:sql}
SELECT id,HEX(a) FROM t1;
{code}
{noformat}
+------+--------+
| id | HEX(a) |
+------+--------+
| 1 | NULL |
+------+--------+
{noformat}
It inserted {{NULL}} into the {{GEOMETRY}} column. Looks fine.

Now do the same with {{FIELDS TERMINATED BY ''}}:
{code:sql}
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 '';
{code}
{noformat}
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 1
{noformat}

It inserted a record, let's check values:
{code:sql}
SELECT id,HEX(a) FROM t1;
{code}
{noformat}
+------+--------+
| id | HEX(a) |
+------+--------+
| 1 | |
+------+--------+
{noformat}

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

I go to MariaDB data directory and create a file consisting of a single digit {{1}} in the database {{test}}:
{noformat}
echo 1 >test/data.txt
{noformat}

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

{code:sql}
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;
{code}
{noformat}
ERROR 1263 (22004): Column set to default value; NULL supplied to NOT NULL column 'a' at row 1
{noformat}

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:

{code:sql}
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 '';
{code}
{noformat}
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 1
{noformat}

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

{code:sql}
SELECT id, HEX(a) FROM t1;
{code}
{noformat}
+------+--------+
| id | HEX(a) |
+------+--------+
| 1 | |
+------+--------+
{noformat}

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:

{code:sql}
SET sql_mode='';
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (id INT, a GEOMETRY);
LOAD DATA INFILE 'data.txt' INTO TABLE t1;
{code}
{noformat}
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 1
{noformat}
Notice, it inserted the record. Let's check values:
{code:sql}
SELECT id,HEX(a) FROM t1;
{code}
{noformat}
+------+--------+
| id | HEX(a) |
+------+--------+
| 1 | NULL |
+------+--------+
{noformat}
It inserted {{NULL}} into the {{GEOMETRY}} column. Looks fine.

Now do the same with {{FIELDS TERMINATED BY ''}}:
{code:sql}
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 '';
{code}
{noformat}
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 1
{noformat}

It inserted a record, let's check values:
{code:sql}
SELECT id,HEX(a) FROM t1;
{code}
{noformat}
+------+--------+
| id | HEX(a) |
+------+--------+
| 1 | |
+------+--------+
{noformat}

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

Alexander Barkov added a comment - 2018-03-07 12:14 - edited

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.

Alexander Barkov added a comment - 2018-03-07 12:14 - edited 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.

Alexander Barkov made changes - 2018-03-07 16:02

issue.field.resolutiondate

2018-03-07 16:02:34.0

2018-03-07 16:02:34.905

Alexander Barkov made changes - 2018-03-07 16:02

Fix Version/s		10.3.6 [ 23003 ]
Fix Version/s	10.3 [ 22126 ]
Resolution		Fixed [ 1 ]
Status	Open [ 1 ]	Closed [ 6 ]

Alexander Barkov added a comment - 2019-02-23 14:44

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

Alexander Barkov added a comment - 2019-02-23 14:44 Backported to 10.2.23, as a part of MDEV-18045 .

Sergei Golubchik made changes - 2021-12-06 21:46

Workflow

MariaDB v3 [ 85882 ]

MariaDB v4 [ 153911 ]

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 1 Start watching this issue

Dates

Created:: 2018-03-07 02:36

Updated:: 2024-10-22 07:12

Resolved:: 2018-03-07 16:02

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration