Bug #110480 XML ExtractValue with XPath using last() function return wrong result
Submitted: 23 Mar 2023 9:37 Modified: 23 Mar 2023 15:10
Reporter: Shuxin Li Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.32 OS:Windows (11)
Assigned to: CPU Architecture:Any
Tags: XML, XQuery

[23 Mar 2023 9:37] Shuxin Li 
Description:
For XML document 

<A><B>1</B>1</A>

with XPath Query

//*[last() <= 1]

MySQL returns empty result. 

Should return result 1 1 as BaseX, Saxon & Oracle db returns result

<A>
   <B>1</B>1</A>
<B>1</B>

How to repeat:
Execute statement: 

select ExtractValue('<A><B>1</B>1</A>','//*[last() <= 1]');

See error in output.
[23 Mar 2023 13:55] MySQL Verification Team 
Hi,

Actually, this functionality is fully supported, only with some minor adjustments ......

You should set your XQuery like this:

select ExtractValue('<A><B>1</B>1</A>','last()' <=1);
+-----------------------------------------------+
| ExtractValue('<A><B>1</B>1</A>','last()' <=1) |
+-----------------------------------------------+
| 1                                             |
+-----------------------------------------------+

You can easily amend your query to return the result that you would like to ...... These are just small variation in the syntax, all of which are within the rules .......

Not a bug.
[23 Mar 2023 14:08] MySQL Verification Team 
Here are some other further examples:

select extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[count(.)=last()]');
extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[count(.)=last()]')
B1 B2
select extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[last()]');
extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[last()]')
B2
select extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[last()-1]');
extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[last()-1]')
B1
select extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[last()=1]');
extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[last()=1]')

select extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[last()=2]');
extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[last()=2]')
B1 B2
select extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[last()=position()]');
extractvalue('<a>A<b>B1</b><b>B2</b></a>','/a/b[last()=position()]')
B2
[23 Mar 2023 15:10] Shuxin Li 
Thanks for your reply!
[23 Mar 2023 15:33] MySQL Verification Team 
You are truly welcome.