Bug #43937 ExtractValue fails for subsequent valid XML values after an invalid XML
Submitted: 29 Mar 2009 8:54 Modified: 2 Apr 2009 5:42
Reporter: Shlomo Priymak Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: XML functions Severity:S2 (Serious)
Version:5.1.34, 6.0.11 OS:Any
Assigned to: Gleb Shchepa CPU Architecture:Any
Tags: ExtractValue, XML
Triage: Triaged: D2 (Serious)

[29 Mar 2009 8:54] Shlomo Priymak
Description:
When using ExtractValue to parse XML, one invalid XML which the function can't parse, makes it return NULL for all subsequent results.

Easily repeatable, no workaround exists, by definition this is S1 or S2.

How to repeat:
mysql> create table xml (xml varchar(1000));
Query OK, 0 rows affected (0.12 sec)

mysql> insert into xml values ('<root><not valid>xml</valid></root>');
Query OK, 1 row affected (0.00 sec)

mysql> insert into xml values ('<root><valid>xml 1</valid></root>');
Query OK, 1 row affected (0.00 sec)

mysql> insert into xml values ('<root><valid>xml 2</valid></root>');
Query OK, 1 row affected (0.00 sec)

mysql> insert into xml values ('<root><valid>xml 3</valid></root>');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM xml WHERE ExtractValue(xml,'/root/valid') IS NOT NULL;
Empty set, 1 warning (0.00 sec)

mysql>
mysql> DELETE FROM xml WHERE xml = '<root><not valid>xml</valid></root>';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM xml WHERE ExtractValue(xml,'/root/valid') IS NOT NULL;
+-----------------------------------+
| xml                               |
+-----------------------------------+
| <root><valid>xml 1</valid></root> |
| <root><valid>xml 2</valid></root> |
| <root><valid>xml 3</valid></root> |
+-----------------------------------+
3 rows in set (0.00 sec)

mysql>
[29 Mar 2009 9:02] Valeriy Kravchuk
Thank you for the bug report. Verified just as described with recent 5.1.34 from bzr:

valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.34-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table xml (xml varchar(1000));
Query OK, 0 rows affected (0.08 sec)

mysql> insert into xml values ('<root><not valid>xml</valid></root>');
Query OK, 1 row affected (0.00 sec)

mysql> insert into xml values ('<root><valid>xml 1</valid></root>');
Query OK, 1 row affected (0.00 sec)

mysql> insert into xml values ('<root><valid>xml 2</valid></root>');
Query OK, 1 row affected (0.00 sec)

mysql> insert into xml values ('<root><valid>xml 3</valid></root>');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM xml WHERE ExtractValue(xml,'/root/valid') IS NOT NULL;
Empty set, 1 warning (0.00 sec)

mysql> DELETE FROM xml WHERE xml = '<root><not valid>xml</valid></root>';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM xml WHERE ExtractValue(xml,'/root/valid') IS NOT NULL;
+-----------------------------------+
| xml                               |
+-----------------------------------+
| <root><valid>xml 1</valid></root> | 
| <root><valid>xml 2</valid></root> | 
| <root><valid>xml 3</valid></root> | 
+-----------------------------------+
3 rows in set (0.00 sec)
[29 Mar 2009 9:04] Valeriy Kravchuk
Actually, we have a warning:

mysql> SELECT * FROM xml WHERE ExtractValue(xml,'/root/valid') IS NOT NULL;
Empty set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1525
Message: Incorrect XML value: 'parse error at line 1 pos 28: '</valid>' unexpected ('</not>' wanted)'
1 row in set (0.00 sec)

But still it should NOT prevent further rows from being selected.
[2 Apr 2009 5:42] Gleb Shchepa
This bug duplicates bug #43183.