MySQL Bugs: #34105: extractValue does not work correctly

Bug #34105	extractValue does not work correctly
Submitted:	28 Jan 2008 14:07	Modified:	28 Jan 2008 22:13
Reporter:	Steffen Sens	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: XML functions	Severity:	S3 (Non-critical)
Version:	MySql Server 5.1.22, 5.1, 6.0-BK	OS:	Any (Solaris, Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	ExtractValue(), XML

Description:
The function extractValue() works not proper together with using variables or/and sql statement CONCAT()

set only strings as XPATH part, extractValue(<xml>,<xpath>) is working fine 

How to repeat:
examples for illustrating the problem:

1. use of CONCAT() with 2 or more strings with a leading variable
   
mysql> select extractValue(@p_xml,concat(@p_xpath,'[1]/name'));
+--------------------------------------------------+
| extractValue(@p_xml,concat(@p_xpath,'[1]/name')) |
+--------------------------------------------------+
| nottuln5                                         |
+--------------------------------------------------+
1 row in set (0.00 sec)

...but 

mysql> select extractValue(@p_xml,concat(@p_xpath,'[1]/','name'));
+-----------------------------------------------------+
| extractValue(@p_xml,concat(@p_xpath,'[1]/','name')) |
+-----------------------------------------------------+
|                                                     |
+-----------------------------------------------------+
1 row in set (0.00 sec)

2. use of CONCAT() with mix of pure strings and variables

mysql> select @p_column;
+-----------+
| @p_column |
+-----------+
| name      |
+-----------+
1 row in set (0.00 sec)

mysql> select extractValue(@p_xml,concat(@p_xpath,'[1]/name'));
+--------------------------------------------------+
| extractValue(@p_xml,concat(@p_xpath,'[1]/name')) |
+--------------------------------------------------+
| nottuln5                                         |
+--------------------------------------------------+
1 row in set (0.00 sec)

mysql> select extractValue(@p_xml,concat(@p_xpath,'[1]/',@p_column));
+--------------------------------------------------------+
| extractValue(@p_xml,concat(@p_xpath,'[1]/',@p_column)) |
+--------------------------------------------------------+
|                                                        |
+--------------------------------------------------------+
1 row in set (0.00 sec)

3. Use of extractValue() with a variable anstead of a xpath-string

mysql> select @p_xpath;
+----------------------+
| @p_xpath             |
+----------------------+
| //dachs/host[1]/name |
+----------------------+
1 row in set (0.00 sec)

mysql> select extractValue(@p_xml,'//dachs/host[1]/name');
+---------------------------------------------+
| extractValue(@p_xml,'//dachs/host[1]/name') |
+---------------------------------------------+
| nottuln5                                    |
+---------------------------------------------+
1 row in set (0.00 sec)

mysql> select extractValue(@p_xml,@p_xpath);
+-------------------------------+
| extractValue(@p_xml,@p_xpath) |
+-------------------------------+
|                               |
+-------------------------------+
1 row in set (0.00 sec)

###### XML ########

<?xml version='1.0' encoding='iso-8859-1' standalone='yes'?>
<dachs>
  <host>
    <name>nottuln5</name>
   </host>
</dachs>

Suggested fix:
no idea

Thank you for the report.

Verified as described:

$mysql51
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 287
Server version: 5.1.24-rc-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> set @p_xml='<?xml version=\'1.0\' encoding=\'iso-8859-1\' standalone=\'yes\'?><dachs><host><name>nottuln5</name></host></dachs>';
Query OK, 0 rows affected (0.61 sec)

mysql> 
mysql> set @p_xpath = '//dachs/host';
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> set @p_column = 'name';
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> select extractValue(@p_xml,concat(@p_xpath,'[1]/name'));
+--------------------------------------------------+
| extractValue(@p_xml,concat(@p_xpath,'[1]/name')) |
+--------------------------------------------------+
| nottuln5                                         | 
+--------------------------------------------------+
1 row in set (0.14 sec)

mysql> 
mysql> select extractValue(@p_xml,concat(@p_xpath,'[1]/','name'));
+-----------------------------------------------------+
| extractValue(@p_xml,concat(@p_xpath,'[1]/','name')) |
+-----------------------------------------------------+
|                                                     | 
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> 
mysql> select concat(@p_xpath,'[1]/name'), concat(@p_xpath,'[1]/','name');
+-----------------------------+--------------------------------+
| concat(@p_xpath,'[1]/name') | concat(@p_xpath,'[1]/','name') |
+-----------------------------+--------------------------------+
| //dachs/host[1]/name        | //dachs/host[1]/name           | 
+-----------------------------+--------------------------------+
1 row in set (0.00 sec)

mysql> 
mysql> select extractValue(@p_xml,concat(@p_xpath,'[1]/name'));
+--------------------------------------------------+
| extractValue(@p_xml,concat(@p_xpath,'[1]/name')) |
+--------------------------------------------------+
| nottuln5                                         | 
+--------------------------------------------------+
1 row in set (0.01 sec)

mysql> 
mysql> select extractValue(@p_xml,concat(@p_xpath,'[1]/',@p_column));
+--------------------------------------------------------+
| extractValue(@p_xml,concat(@p_xpath,'[1]/',@p_column)) |
+--------------------------------------------------------+
|                                                        | 
+--------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 
mysql> select concat(@p_xpath,'[1]/name'), concat(@p_xpath,'[1]/',@p_column);
+-----------------------------+-----------------------------------+
| concat(@p_xpath,'[1]/name') | concat(@p_xpath,'[1]/',@p_column) |
+-----------------------------+-----------------------------------+
| //dachs/host[1]/name        | //dachs/host[1]/name              | 
+-----------------------------+-----------------------------------+
1 row in set (0.00 sec)

mysql> 
mysql> set @p_xpath = '//dachs/host[1]/name';
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> select extractValue(@p_xml,'//dachs/host[1]/name');
+---------------------------------------------+
| extractValue(@p_xml,'//dachs/host[1]/name') |
+---------------------------------------------+
| nottuln5                                    | 
+---------------------------------------------+
1 row in set (0.00 sec)

mysql> 
mysql> select extractValue(@p_xml,@p_xpath);
+-------------------------------+
| extractValue(@p_xml,@p_xpath) |
+-------------------------------+
|                               | 
+-------------------------------+
1 row in set (0.00 sec)

mysql> 
mysql> select '//dachs/host[1]/name', @p_xpath;
+----------------------+----------------------+
| //dachs/host[1]/name | @p_xpath             |
+----------------------+----------------------+
| //dachs/host[1]/name | //dachs/host[1]/name | 
+----------------------+----------------------+
1 row in set (0.00 sec)

Also reproduced on MySQL 5.5. When this going to be fixed?

When (see last comment from 2008) ??

How do I properly select a value from an XML using MySQL functions?