Bug #32911 Variable used as XPath expressions treated as empty string
Submitted: 2 Dec 2007 13:27 Modified: 2 Dec 2007 15:04
Reporter: Jon Stephens Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: XML functions Severity:S3 (Non-critical)
Version:5.1, 6.0 OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Tags: Contribution, ExtractValue(), UpdateXML(), variables, XML, XPath

[2 Dec 2007 13:27] Jon Stephens
Description:
It is not possible to use a variable to hold the XPath expression used by ExtractValue() and UpdateXML(). With these functions, such a variable is treated as an empty string. See "Description" for examples.

How to repeat:
mysql> SHOW VARIABLES LIKE 'version%';
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| version                 | 6.0.4-alpha-debug   |
| version_comment         | Source distribution |
| version_compile_machine | i686                |
| version_compile_os      | suse-linux-gnu      |
+-------------------------+---------------------+
4 rows in set (0.00 sec)

mysql> SELECT ExtractValue('<a>X</a><b>Y</b>', '//a');
+-----------------------------------------+
| ExtractValue('<a>X</a><b>Y</b>', '//a') |
+-----------------------------------------+
| X                                       |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> SET @xml='<a>X</a><b>Y</b>';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @xpath = '//a';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT ExtractValue(@xml, '//a');
+---------------------------+
| ExtractValue(@xml, '//a') |
+---------------------------+
| X                         |
+---------------------------+
1 row in set (0.00 sec)

# Result for next 2 queries should be: X

mysql> SELECT ExtractValue('<a>X</a><b>Y</b>', @xpath);
+------------------------------------------+
| ExtractValue('<a>X</a><b>Y</b>', @xpath) |
+------------------------------------------+
|                                          |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT ExtractValue(@xml, @xpath);
+----------------------------+
| ExtractValue(@xml, @xpath) |
+----------------------------+
|                            |
+----------------------------+
1 row in set (0.00 sec)

mysql> SELECT UpdateXML('<a>X</a><b>Y</b>', '//a', '<c>Z</c>');
+--------------------------------------------------+
| UpdateXML('<a>X</a><b>Y</b>', '//a', '<c>Z</c>') |
+--------------------------------------------------+
| <c>Z</c><b>Y</b>                                 |
+--------------------------------------------------+
1 row in set (0.00 sec)

mysql> SET @new='<c>Z</c>';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT UpdateXML(@xml, '//a', @new);
+------------------------------+
| UpdateXML(@xml, '//a', @new) |
+------------------------------+
| <c>Z</c><b>Y</b>             |
+------------------------------+
1 row in set (0.00 sec)

# Result of next query should also be: <c>Z</c><b>Y</b>

mysql> SELECT UpdateXML(@xml, @xpath, @new);
+-------------------------------+
| UpdateXML(@xml, @xpath, @new) |
+-------------------------------+
| <a>X</a><b>Y</b>              |
+-------------------------------+
1 row in set (0.00 sec)

mysql> SELECT @xml, @xpath, @new;
+------------------+--------+----------+
| @xml             | @xpath | @new     |
+------------------+--------+----------+
| <a>X</a><b>Y</b> | //a    | <c>Z</c> |
+------------------+--------+----------+
1 row in set (0.00 sec)

mysql> exit
Bye

jon@flundra:~/bin/mysql-6.0/bin> ls -l ../libexec/mysqld
-rwxr-xr-x 1 jon users 43944329 2007-11-26 11:43 ../libexec/mysqld 

Suggested fix:
Allow XPath expressions to be stored as string variables.
[2 Dec 2007 13:28] Jon Stephens
Following discussion with Bar, I'm documenting this as a known limitation that we intend to fix.
[11 Dec 2007 2:51] xiaoxin yang
reason for this bug:

the node_name of Item_nodeset_func_axisbyname is initialized of the
address of n_arg which is passed from a temporary value "String" in
Item_xml_str_func::fix_length_and_dec():

2306 void Item_xml_str_func::fix_length_and_dec()
2307 {
2308   String *xp, tmp;
...
2333   xp= args[1]->val_str(&tmp);
...
2339   rc= my_xpath_parse(&xpath, xp->ptr(), xp->ptr() + xp->length());

236 class Item_nodeset_func_axisbyname :public Item_nodeset_func
237 {
238   const char *node_name;
239   uint node_namelen;
240 public:
241   Item_nodeset_func_axisbyname(Item *a, const char *n_arg, uint l_arg,
242                                String *pxml):
243     Item_nodeset_func(a, pxml), node_name(n_arg), node_namelen(l_arg) { }
...
252 };

when Item_xml_str_func::fix_length_and_dec returned, the value of
node_name is gone, so a simple patch for this can be like as:

--- item_xmlfunc.cc.orig        2006-02-27 21:25:09.000000000 +0000
+++ item_xmlfunc.cc     2007-12-11 02:07:40.000000000 +0000
@@ -2305,7 +2305,7 @@

 void Item_xml_str_func::fix_length_and_dec()
 {
-  String *xp, tmp;
+  static String *xp, tmp;
 MY_XPATH xpath;
 int rc;

test:
mysql>  SET @xpath = '//a';
Query OK, 0 rows affected (0.00 sec)

mysql>  SET @xml='<a>X</a><b>Y</b>';
Query OK, 0 rows affected (0.00 sec)

mysql>  SELECT ExtractValue(@xml, '//a');
+---------------------------+
| ExtractValue(@xml, '//a') |
+---------------------------+
| X                         |
+---------------------------+
1 row in set (0.00 sec)

mysql>  SELECT ExtractValue(@xml, @xpath);
+----------------------------+
| ExtractValue(@xml, @xpath) |
+----------------------------+
| X                          |
+----------------------------+
1 row in set (0.00 sec)

but this patch maybe is not thread safe
[20 Feb 2020 15:40] Penny Borelli
This bug has still not been fixed. Please see below.

ExtractValue(tblEntry.d_EntryXML, concat('/Row/FieldEntry[@fieldID="', tblFormField.d_FieldID,'"]'))

I get this error.

Error Code: 1105. Only constant XPATH queries are supported