[MDEV-14628] Wrong autoinc value assigned by LOAD XML in the NO_AUTO_VALUE_ON_ZERO mode Created: 2017-12-12  Updated: 2019-02-23  Resolved: 2017-12-13

Status: Closed
Project: MariaDB Server
Component/s: Data types
Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3
Fix Version/s: 10.3.3

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-15497 Wrong empty value in a GEOMETRY colum... Closed

 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.

 Comments   
Comment by Alexander Barkov [ 2017-12-13 ]

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.

Comment by Alexander Barkov [ 2019-02-23 ]

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

Generated at Thu Feb 08 08:15:03 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.