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

Wrong empty value in a GEOMETRY column on LOAD DATA

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

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-14628 Wrong autoinc value assigned by LOAD XML in the NO_AUTO_VALUE_ON_ZERO mode

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15479 Empty string is erroneously allowed as a GEOMETRY column value

          • Major - Major loss of function.
          • Stalled

          Activity

            Ascending order - Click to sort in descending order
            bar Alexander Barkov added a comment - - 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.

            bar Alexander Barkov added a comment - - 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.
            bar Alexander Barkov added a comment -

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

            bar Alexander Barkov added a comment - Backported to 10.2.23, as a part of MDEV-18045 .

            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.