MySQL Bugs: #79988: ExtractValue return NULL instead empty string when do not find a XML path

Bug #79988	ExtractValue return NULL instead empty string when do not find a XML path
Submitted:	14 Jan 2016 15:38	Modified:	29 Jan 2016 18:46
Reporter:	Roni Gonzalez	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S1 (Critical)
Version:	5.6, 5.6.28	OS:	Ubuntu (12.04 LTS)
Assigned to:		CPU Architecture:	Any
Tags:	ExtractValue, mysql 5.6

Description:
In description of ExtractValue is indicated that :

"If no matching text node is found for the expression (including the implicit /text())—for whatever reason, as long as xpath_expr is valid, and xml_frag consists of elements which are properly nested and closed—an empty string is returned. No distinction is made between a match on an empty element and no match at all. This is by design. "

In fact in MSQL 5.5 this works correclty.
IN 5.6 a NULL string is returned.

All exptected operations fails after this Ej:

if( @result = '' ) ..  -> NULL it not ''

select concat( @result, ' hi ' );  -> now is null before was blank + 'hi'.

How to repeat:

IN SQL 5.5

set @x = '<MESSAGE><DATA1>HOLA</DATA1></MESSAGE>';
set @y = 'Default Value';
select ExtractValue( @x, '/MESSAGE/DATA2' ) into @y;
select concat( 'test ' , @y );

result of @y is '' -> concat = test;

IN 5.6

set @x = '<MESSAGE><DATA1>HOLA</DATA1></MESSAGE>';
set @y = 'Default Value';
select ExtractValue( @x, '/MESSAGE/DATA2' ) into @y;
select concat( 'test ' , @y );

result of @y is null -> concat = null;

Suggested fix:

Return an empty string when a Xpath is not found as is described in documentation

Hello Roni Gonzalez,

Thank you for the report and test case.
Observed that 5.6.28 is affected.

Thanks,
Umesh

-- 5.5/5.7 latest builds not affected

-- 5.5.48

[umshastr@hod03]/export/umesh/server/binaries/mysql-5.5.48:  bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.48 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> drop database test;
Query OK, 0 rows affected (0.00 sec)

mysql> set @x = '<MESSAGE><DATA1>HOLA</DATA1></MESSAGE>';
Query OK, 0 rows affected (0.00 sec)

mysql> set @y = 'Default Value';
Query OK, 0 rows affected (0.00 sec)

mysql> select ExtractValue( @x, '/MESSAGE/DATA2' ) into @y;
Query OK, 1 row affected (0.00 sec)

mysql> select concat( 'test ' , @y );
+------------------------+
| concat( 'test ' , @y ) |
+------------------------+
| test                   |
+------------------------+
1 row in set (0.00 sec)

mysql>

-- 5.6.28

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.28:  bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.28-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> set @x = '<MESSAGE><DATA1>HOLA</DATA1></MESSAGE>';
Query OK, 0 rows affected (0.00 sec)

mysql> set @y = 'Default Value';
Query OK, 0 rows affected (0.00 sec)

mysql> select ExtractValue( @x, '/MESSAGE/DATA2' ) into @y;
Query OK, 1 row affected (0.00 sec)

mysql> select concat( 'test ' , @y );
+------------------------+
| concat( 'test ' , @y ) |
+------------------------+
| NULL                   |
+------------------------+
1 row in set (0.00 sec)

mysql>

-- 5.7.10

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.10:  bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.10-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> set @x = '<MESSAGE><DATA1>HOLA</DATA1></MESSAGE>';
Query OK, 0 rows affected (0.00 sec)

mysql> set @y = 'Default Value';
Query OK, 0 rows affected (0.00 sec)

mysql> select ExtractValue( @x, '/MESSAGE/DATA2' ) into @y;
Query OK, 1 row affected (0.00 sec)

mysql> select concat( 'test ' , @y );
+------------------------+
| concat( 'test ' , @y ) |
+------------------------+
| test                   |
+------------------------+
1 row in set (0.00 sec)

mysql>

Posted by developer:
 
This is a duplicate of internal bug
Bug #13358486 WEIGHT_STRING = MY_STRNXFRM_UNICODE: ASSERTION `SRC' FAILED.
Which was fixed in mysql 5.7.6

A fix will be provided for versión 5.6 ?

Posted by developer:
 
commit c1cc22e38ee7b24bfd0856bb3a09de286d2ebf56
Author: Tor Didriksen <tor.didriksen@oracle.com>
Date:   Mon Jan 18 15:11:41 2016 +0100

    Bug#22552615 EXTRACTVALUE RETURNS NULL WHEN NO MATCHING TEXT NODE IS FOUND FOR THE EXPRESSION
    
    This is a backport of the patch for
    Bug #13358486 WEIGHT_STRING = MY_STRNXFRM_UNICODE: ASSERTION `SRC' FAILED.

Noted in 5.6.30 changelog.

Certain queries could raise an assertion when a internal string
operation produced a NULL pointer rather than an empty string.