MySQL Bugs: #43183: ExctractValue() brings result list in missorder

Bug #43183	ExctractValue() brings result list in missorder
Submitted:	25 Feb 2009 12:22	Modified:	6 May 2009 14:41
Reporter:	Alex Aulbach	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: XML functions	Severity:	S2 (Serious)
Version:	5.1.22-rc-log, 5.1.31, 5.1, 6.0 bzr	OS:	Any
Assigned to:	Sergei Glukhov	CPU Architecture:	Any
Tags:	xml xpath extractvalue

Description:
When performing this query, mysql goes really crazy:

mysql> SELECT ExtractValue( xml, '/object/boilers/boiler[1]/@name' ) e1, ExtractValue( xml, '(/object/control/remotecontrol[1]/@name)|(/object/control/ui[1]/@name)' ) e2, ExtractValue( xml, '/object/kommbox/box/@name') e3, kb.sernum kb_sernum, xml.sernum xml_sernum, left(xml,20) xml_left FROM vw_kommbox kb LEFT JOIN vw_xmllayout_cache xml ON kb.sernum = xml.sernum limit 30;
+------------+----------+------------+-------------------------------------+-------------------------------------+----------------------+
| e1         | e2       | e3         | kb_sernum                           | xml_sernum                          | xml_left             |
+------------+----------+------------+-------------------------------------+-------------------------------------+----------------------+
| BAI        | 400      | 175        | sernum:21051100200039880082005215N4 | sernum:21051100200039880082005215N4 | <?xml version="1.0"  |
| BAI        | SOLSY_UI | 0020003718 | sernum:21052400200037180082005753N2 | sernum:21052400200037180082005753N2 | <?xml version="1.0"  |
| BAI        |          | 0020003987 | sernum:21054600200039870082005780N8 | sernum:21054600200039870082005780N8 | <?xml version="1.0"  |
| BAI        | SOLSY_UI | 0020003718 | sernum:21060600200037180082005306N5 | sernum:21060600200037180082005306N5 | <?xml version="1.0"  |
| 308523     | 400      | 0020003718 | sernum:21060600200037180082005315N4 | sernum:21060600200037180082005315N4 | <?xml version="1.0"  |
| 308523     | 400      | 0020003718 | sernum:21060600200037180082005320N9 | sernum:21060600200037180082005320N9 | <?xml version="1.0"  |
| 308523     | 400      | 0020003718 | sernum:21060600200037180082005321N5 | sernum:21060600200037180082005321N5 | <?xml version="1.0"  |
| 308523     | 430      | 0020003718 | sernum:21060600200037180082005326N5 | sernum:21060600200037180082005326N5 | <?xml version="1.0"  |
| 308523     | 360      | 0020003718 | sernum:21060600200037180082005327N1 | sernum:21060600200037180082005327N1 | <?xml version="1.0"  |
| 308523     | 392      | 0020003718 | sernum:21060600200037180082005328N7 | sernum:21060600200037180082005328N7 | <?xml version="1.0"  |
| 308523     | 400      | 0020003718 | sernum:21060600200037180082005330N4 | sernum:21060600200037180082005330N4 | <?xml version="1.0"  |
| 308523     | 360      | 0020003718 | sernum:21060600200037180082005331N0 | sernum:21060600200037180082005331N0 | <?xml version="1.0"  |
| 308523     | 400      | 0020003718 | sernum:21060600200037180082005332N6 | sernum:21060600200037180082005332N6 | <?xml version="1.0"  |
| 308523     | 400      | 0020003718 | sernum:21060600200037180082005335N4 | sernum:21060600200037180082005335N4 | <?xml version="1.0"  |
| 308523     | 430      | 0020003718 | sernum:21065000200037180082005836N9 | sernum:21065000200037180082005836N9 | <?xml version="1.0"  |
| 308523     | SOLSY_UI | 0020003718 | sernum:21070200200037180082006066N6 | sernum:21070200200037180082006066N6 | <?xml version="1.0"  |
| 308523     | 430      | 0020003718 | sernum:21070200200037180082006067N2 | sernum:21070200200037180082006067N2 | <?xml version="1.0"  |
|            |          | 0020003718 | sernum:21070200200037180082006075N5 | sernum:21070200200037180082006075N5 | <?xml version="1.0"  |
| 308523     |          | 0020003718 | sernum:21070200200037180082006096N1 | sernum:21070200200037180082006096N1 | <?xml version="1.0"  |
| NULL       | NULL     | NULL       | sernum:21070200200037180082006104N8 | NULL                                | NULL                 |
| NULL       | NULL     | NULL       | sernum:21074200200037180082007504N7 | NULL                                | NULL                 |
| NULL       | NULL     | NULL       | sernum:21044500200039870082005049N4 | NULL                                | NULL                 |
| BAI        | NULL     |            | NULL                                | 175                                 | NULL                 |
|            | NULL     |            | NULL                                | 0020003718                          | NULL                 |
| BAI        | NULL     |            | NULL                                | 0020003718                          | NULL                 |
| 308523     | NULL     |            | NULL                                | 0020003718                          | NULL                 |
| 308523     | NULL     | 36f        | NULL                                | 0020003718                          | NULL                 |
| 0010006510 | NULL     |            | NULL                                | 0020003719                          | NULL                 |
| 0010005331 | NULL     | E7f        | NULL                                | 0020003719                          | NULL                 |
| 0010005322 | NULL     | BSz        | NULL                                | 0020003719                          | NULL                 |
+------------+----------+------------+-------------------------------------+-------------------------------------+----------------------+

As you might see: There is some very, very strange mixing of the columns here. For example: The column e3 holds values, even if the xml_left column is empty. The column e2 is NULL after some "things" happening in row 20, but xml_sernum represents the result-value of that row after that row 20.

How to repeat:
Just use more than one ExtractValue() in a select. I'll always get strange results after some rows. If I drop the ExtractValue-Functions from the select, all works fine.

I can give you some example records, but I'm sure, you can create your testcase for this with this description by yourself.

Maybe this is already fixed. If this bug is already fixed, please tell me the version. I can only test here with above version and this would be a good reason to change. :)

I evaluated now the following important thing:

update tmpbla set ErsterBoiler = ExtractValue( xml, '/object/boilers/boiler[1]/@name' )

--->  8 records changed. I awaited 5191 records. I looked, where the last change happend: The XML for that row was just: '<?xml version="1.0">'

So I changed the query like this:
update tmpbla set ErsterBoiler = ExtractValue( xml, '/object/boilers/boiler[1]/@name' ) where xml != '<?xml version="1.0">'

---> 5189 records changed as awaited.

Solution seems to be clear: Even for one invalid XML, the query has not to abort the complete work.

Thank you for the bug report. Could you please try new server version and if you still get the same result please provide complete test case (dump file). Thanks in advance.

I cannot promise to change within the next weeks. Please try this out yourself:

This table:

CREATE TABLE IF NOT EXISTS `blub` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `xml` text,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;
INSERT INTO `blub` (`id`, `xml`) VALUES
(10, '<?xml version="1.0"?><bla></bla>'),
(11, '<?xml version="1.0"?><bla name="blubb"></bla>'),
(15, '<?xml version="1.0" ?><bla name="jigi"></bla>'),
(14, '<xml version="kaputt">');

This query:

SELECT xml,extractvalue(xml,'/bla/@name') as r1,extractvalue(xml,'/bla/@name') as r2,extractvalue(xml,'/bla/@name') as r3 FROM `blub` ORDER BY `blub`.`id` ASC

My result is, that the last row of r2 is empty. Which is obviously wrong.

Try also out this:

SELECT extractvalue( xml, '/bla/@name' ) AS r1, extractvalue( xml, '/bla/@name' ) AS r2, extractvalue( xml, '/bla/@name' ) AS r3, xml
FROM `blub`
ORDER BY `blub`.`id` ASC

Now in the last row also xml is empty. The resultset is completly shuffeled after that.

I read in the bugfixes, that you have added handling of wrong xml. I think they have added this bug doing that.

Thank you for the feedback.

Verified with version 5.1.31 as described. Without ORDER BY results are correct.

Debug build of current development sources fails with following backtrace:

Program terminated with signal 6, Aborted.

warning: svr4_current_sos: Can't read pathname for load map: Input/output error

#0  0x002ce402 in __kernel_vsyscall ()
#0  0x002ce402 in __kernel_vsyscall ()
#1  0x0046264f in pthread_kill () from /lib/libpthread.so.0
#2  0x085a3775 in my_write_core (sig=6) at stacktrace.c:310
#3  0x0824c139 in handle_segfault (sig=6) at mysqld.cc:2505
#4  <signal handler called>
#5  0x002ce402 in __kernel_vsyscall ()
#6  0x00314f90 in raise () from /lib/libc.so.6
#7  0x00316678 in abort () from /lib/libc.so.6
#8  0x0030e269 in __assert_fail () from /lib/libc.so.6
#9  0x08242cbc in Protocol_text::store (this=0xa3604d4, from=0xa3b2988 "jigi", length=4, fromcs=0x88657c0) at protocol.cc:793
#10 0x081906cf in Item::send (this=0xa3e45a8, protocol=0xa3604d4, buffer=0xb742fb9c) at item.cc:5280
#11 0x0823465a in select_send::send_data (this=0xa3e49d0, items=@0xa3615f0) at sql_class.cc:1584
#12 0x082d95d2 in end_send (join=0xa3e49e8, join_tab=0xa3c2c98, end_of_records=false) at sql_select.cc:11942
#13 0x082d562b in evaluate_join_record (join=0xa3e49e8, join_tab=0xa3c2b10, error=0) at sql_select.cc:11202
#14 0x082d59f4 in sub_select (join=0xa3e49e8, join_tab=0xa3c2b10, end_of_records=false) at sql_select.cc:11093
#15 0x082d5dbf in do_select (join=0xa3e49e8, fields=0xa3615f0, table=0x0, procedure=0x0) at sql_select.cc:10843
#16 0x082ed37f in JOIN::exec (this=0xa3e49e8) at sql_select.cc:2199
#17 0x082ed809 in mysql_select (thd=0xa3601c8, rref_pointer_array=0xa361654, tables=0xa3e46a0, wild_num=0, fields=@0xa3615f0, conds=0x0, og_num=1, order=0xa3e4940, group=0x0, having=0x0, proc_param=0x0, 
    select_options=2147764736, result=0xa3e49d0, unit=0xa3612ec, select_lex=0xa36155c) at sql_select.cc:2378
#18 0x082edb57 in handle_select (thd=0xa3601c8, lex=0xa361290, result=0xa3e49d0, setup_tables_done_option=0) at sql_select.cc:268
#19 0x0825b23b in execute_sqlcom_select (thd=0xa3601c8, all_tables=0xa3e46a0) at sql_parse.cc:4910
#20 0x082614f1 in mysql_execute_command (thd=0xa3601c8) at sql_parse.cc:2204
#21 0x0826a85e in mysql_parse (thd=0xa3601c8, 
    inBuf=0xa3e3d80 "SELECT xml,extractvalue(xml,'/bla/@name') as r1,extractvalue(xml,'/bla/@name') as r2,extractvalue(xml,'/bla/@name') as r3 FROM `blub` ORDER BY `blub`.`id` ASC", length=158, 
    found_semicolon=0xb74312fc) at sql_parse.cc:5810
#22 0x0826b49a in dispatch_command (command=COM_QUERY, thd=0xa3601c8, packet=0xa3ae1b9 "", packet_length=158) at sql_parse.cc:1216
#23 0x0826c6b8 in do_command (thd=0xa3601c8) at sql_parse.cc:857
#24 0x08259315 in handle_one_connection (arg=0xa3601c8) at sql_connect.cc:1115
#25 0x0045fbd4 in start_thread () from /lib/libpthread.so.0
#26 0x003b74fe in clone () from /lib/libc.so.6

Debug 6.0 build crashes as well.

Crash was caused by query provided.

Test case for our suite which leads to crash:

$cat src/tests/archive/bug43183.test 
CREATE TABLE IF NOT EXISTS `blub` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `xml` text,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;
INSERT INTO `blub` (`id`, `xml`) VALUES
(10, '<?xml version="1.0"?><bla></bla>'),
(11, '<?xml version="1.0"?><bla name="blubb"></bla>'),
(15, '<?xml version="1.0" ?><bla name="jigi"></bla>'),
(14, '<xml version="kaputt">');

--vertical_results

SELECT extractvalue( xml, '/bla/@name' ) AS r1 FROM `blub` ORDER BY `blub`.`id` ASC;

SELECT xml,extractvalue(xml,'/bla/@name') as r1,extractvalue(xml,'/bla/@name') as r2,extractvalue(xml,'/bla/@name') as r3 FROM `blub` ORDER BY `blub`.`id` ASC;

SELECT extractvalue( xml, '/bla/@name' ) AS r1, extractvalue( xml, '/bla/@name' ) AS r2,
extractvalue( xml, '/bla/@name' ) AS r3, xml
FROM `blub`
ORDER BY `blub`.`id` ASC
;

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/71027

2841 Sergey Glukhov	2009-04-01
      Bug#43183 ExctractValue() brings result list in missorder
      The problem is that XML functions(items) do not reset null_value
      before their execution and further item excution may use
      null_value value of the previous result.
      The fix is to reset null_value.
     @ mysql-test/r/xml.result
        test result
     @ mysql-test/t/xml.test
        test case
     @ sql/item_xmlfunc.cc
        The problem is that XML functions(items) do not reset null_value
        before their execution and further item excution may use
        null_value value of the previous result.
        The fix is to reset null_value.

http://lists.mysql.com/commits/71027 is OK to push

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/71032

2842 Sergey Glukhov	2009-04-01
      Bug#43183 ExctractValue() brings result list in missorder
      The problem is that XML functions(items) do not reset null_value
      before their execution and further item excution may use
      null_value value of the previous result.
      The fix is to reset null_value.
     @ mysql-test/r/xml.result
        test result
     @ mysql-test/t/xml.test
        test case
     @ sql/item_xmlfunc.cc
        The problem is that XML functions(items) do not reset null_value
        before their execution and further item excution may use
        null_value value of the previous result.
        The fix is to reset null_value.

Also see bug #43937 (duplicate).

Pushed into 5.1.35 (revid:davi.arnaut@sun.com-20090505190206-9xmh7dlc6kom8exp) (version source revid:davi.arnaut@sun.com-20090505190206-9xmh7dlc6kom8exp) (merge vers: 5.1.35) (pib:6)

Documented bugfix in the 5.1.35 changelog as follows:

        Using an XML function such as ExtractValue() more than once in a
        single query could produce erroneous results.

Set status NDI: waiting for push to 6.0 tree for version info.

Pushed into 6.0.12-alpha (revid:svoj@sun.com-20090506125450-yokcmvqf2g7jhujq) (version source revid:sergey.glukhov@sun.com-20090401085355-gjl1se5oqcl68ylv) (merge vers: 6.0.11-alpha) (pib:6)

ALso documented in the 6.0.12 changelog. Closed.

Pushed into 5.1.35-ndb-6.3.26 (revid:jonas@mysql.com-20090615074202-0r5r2jmi83tww6sf) (version source revid:jonas@mysql.com-20090615070837-9pccutgc7repvb4d) (merge vers: 5.1.35-ndb-6.3.26) (pib:6)

Pushed into 5.1.35-ndb-7.0.7 (revid:jonas@mysql.com-20090615074335-9hcltksp5cu5fucn) (version source revid:jonas@mysql.com-20090615072714-rmfkvrbbipd9r32c) (merge vers: 5.1.35-ndb-7.0.7) (pib:6)

Pushed into 5.1.35-ndb-6.2.19 (revid:jonas@mysql.com-20090615061520-sq7ds4yw299ggugm) (version source revid:jonas@mysql.com-20090615054654-ebgpz7elwu1xj36j) (merge vers: 5.1.35-ndb-6.2.19) (pib:6)

Pushed into 5.1.37 (revid:build@mysql.com-20090710231213-9guqdu0avc0uwdkp) (version source revid:build@mysql.com-20090710231213-9guqdu0avc0uwdkp) (merge vers: 5.1.37) (pib:11)

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090723102221-ps4uaphwbxzj8p0q) (version source revid:joerg@mysql.com-20090721145751-rqqnhv0kage18wfi) (merge vers: 5.4.4-alpha) (pib:11)

Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)

Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)

Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)