Bug #67313 XML functions: xpath not working with stored functions (regression from 5.5)
Submitted: 21 Oct 2012 13:43 Modified: 11 Dec 2012 12:23
Reporter: Shlomi Noach (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.6.7-rc OS:Any
Assigned to: CPU Architecture:Any
Tags: ExtractValue, updatexml, XML

[21 Oct 2012 13:43] Shlomi Noach
Description:
in 5.6.7, one cannot use an XPath expression that is, or contains, the output of a stored function.

Compare the following:
1. No function
2. Internal TRIM() function
3. Stored function

mysql> select ExtractValue('<a><b>7</b></a>', concat('/a/', 'b'));
+-----------------------------------------------------+
| ExtractValue('<a><b>7</b></a>', concat('/a/', 'b')) |
+-----------------------------------------------------+
| 7                                                   |
+-----------------------------------------------------+

mysql> select ExtractValue('<a><b>7</b></a>', concat('/a/', trim('b')));
+-----------------------------------------------------------+
| ExtractValue('<a><b>7</b></a>', concat('/a/', trim('b'))) |
+-----------------------------------------------------------+
| 7                                                         |
+-----------------------------------------------------------+

mysql> select ExtractValue('<a><b>7</b></a>', concat('/a/', same_same('b')));
ERROR 1105 (HY000): Only constant XPATH queries are supported

See definition for same_same() following;

I have tried with various character sets and collations; but apparently the mere existence of a stored function is enough to abort the operation.

Everything works well on 5.1 & 5.5

How to repeat:

DELIMITER $$

DROP FUNCTION IF EXISTS same_same $$
CREATE FUNCTION same_same(txt varchar(111) CHARSET ascii) RETURNS varchar(111) CHARSET ascii 
DETERMINISTIC
NO SQL
SQL SECURITY INVOKER

begin
  return txt;
end $$

DELIMITER ;

Suggested fix:
Problem is, due to bug #32911, I can't use a session variable to fix that
[11 Dec 2012 12:23] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html
[11 Dec 2012 12:23] Jon Stephens
Documented fix as follows in the 5.6.10 and 5.7.1 changelogs:

        When used with an XPath expression that was, or contained, the
        output of a stored function, ExtractValue() failed with the
        error -Only constant XPATH queries are supported-.

Closed.