Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-15497

Wrong empty value in a GEOMETRY column on LOAD DATA

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3
    • 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

            Activity

              People

              Assignee:
              bar Alexander Barkov
              Reporter:
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: