Bug #110480 XML ExtractValue with XPath using last() function return wrong result
Submitted: 23 Mar 9:37 Modified: 23 Mar 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 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 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 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 15:10] Shuxin Li
Thanks for your reply!
[23 Mar 15:33] MySQL Verification Team
You are truly welcome.