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

Wrong empty value in a GEOMETRY column on LOAD DATA

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
    • 10.3.6
    • Data types, GIS
    • 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

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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