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

Wrong autoinc value assigned by LOAD XML in the NO_AUTO_VALUE_ON_ZERO mode

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.3
    • Data types
    • None

    Description

      This is a similar problem to one that was earlier fixed in MySQL-5.0 under terms of:
      Bug#27586 Wrong autoinc value assigned by LOAD DATA in the NO_AUTO_VALUE_ON_ZERO mode

      Now for LOAD XML instead of LOAD DATA.

      I put a file MYSQL_HOME_DIR/test/t1.xml with this content:

      <list>
      		 
        <row a="1" b="bbb1"/>
      		 
        <row b="bbb2"/>
      		 
      </list>

      and run this script:

      SET @@SQL_MODE=NO_AUTO_VALUE_ON_ZERO;
      		 
      CREATE OR REPLACE TABLE t1(a INT AUTO_INCREMENT PRIMARY KEY, b TEXT);
      		 
      LOAD XML INFILE 't1.xml' INTO TABLE t1 ROWS IDENTIFIED BY '<row>';
      		 
      SELECT * FROM t1 ORDER BY b;

      It returns the following data:

      +---+------+
      		 
      | a | b    |
      		 
      +---+------+
      		 
      | 1 | bbb1 |
      		 
      | 0 | bbb2 |
      		 
      +---+------+

      The auto-increment value for the second record is wrong. It should be 2 rather than 0.

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15497 Wrong empty value in a GEOMETRY column on LOAD DATA

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            bar Alexander Barkov created issue -
            bar Alexander Barkov added a comment - - edited

            A related problem happens with a GEOMETRY column.

            I put this XML file into MYSQL_DATA_DIR/test/a.xml:

            <list>
            		 
              <row id="1"/>
            		 
            </list>

            And run this script:

            SET sql_mode='';
            		 
            CREATE OR REPLACE TABLE t1 (id INT, g GEOMETRY NOT NULL);
            		 
            LOAD XML INFILE 'a.xml' INTO TABLE t1 ROWS IDENTIFIED BY '<row>';
            		 
            SELECT * FROM t1;


            +------+---+
            		 
            | id   | g |
            		 
            +------+---+
            		 
            |    1 |   |
            		 
            +------+---+

            Notice, it returns a wrong result. An empty string is not a valid GEOMETRY value.
            The expected result is to return an error for LOAD XML.

            LOAD DATA correctly returns an error in the same context:
            See the patch for bug#22372.

            bar Alexander Barkov added a comment - - edited A related problem happens with a GEOMETRY column. I put this XML file into MYSQL_DATA_DIR/test/a.xml : < list > < row id = "1" /> </ list > And run this script: SET sql_mode= '' ; CREATE OR REPLACE TABLE t1 (id INT , g GEOMETRY NOT NULL ); LOAD XML INFILE 'a.xml' INTO TABLE t1 ROWS IDENTIFIED BY '<row>' ; SELECT * FROM t1; +------+---+ | id | g | +------+---+ | 1 | | +------+---+ Notice, it returns a wrong result. An empty string is not a valid GEOMETRY value. The expected result is to return an error for LOAD XML . LOAD DATA correctly returns an error in the same context: See the patch for bug#22372.
            bar Alexander Barkov made changes -
            Field Original Value New Value
            issue.field.resolutiondate 2017-12-13 09:23:42.0 2017-12-13 09:23:42.133
            bar Alexander Barkov made changes -
            Component/s Data types [ 13906 ]
            Fix Version/s 10.3.3 [ 22644 ]
            Fix Version/s 10.3 [ 22126 ]
            Resolution Fixed [ 1 ]
            Status Open [ 1 ] Closed [ 6 ]
            bar Alexander Barkov made changes -
            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 .
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 84388 ] MariaDB v4 [ 153336 ]

            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.