Thread: BUG #15908: Xpath operations fail

The following bug has been logged on the website:

Bug reference:      15908
Logged by:          Rick Vincent
Email address:      rvincent@temenos.com
PostgreSQL version: 10.9
Operating system:   Windows
Description:

Hi,

We are using xpath with PostreSQL version 10.9 and have run into a variety
of problems.  The following script highlights the bugs, the most important
of which is that it seems xpath operators >=, >, <=, < are not working
correctly.

DROP TABLE test CASCADE;

CREATE TABLE test
(
    recid integer NOT NULL,
    xmlrecord xml NOT NULL,
    CONSTRAINT test_pkey PRIMARY KEY (recid)
);

insert into test(recid, xmlrecord) 
values(1, XMLPARSE (DOCUMENT '<?xml
version="1.0"?><row><c1>Manual</c1><c2>1</c2><c2 m="2">2</c2></row>'));
insert into test(recid, xmlrecord) 
values(2, XMLPARSE (DOCUMENT '<?xml
version="1.0"?><row><c1>Apropos</c1><c2>1</c2><c2 m="2">2</c2></row>'));
insert into test(recid, xmlrecord) 
values(3, XMLPARSE (DOCUMENT '<?xml
version="1.0"?><row><c1>Bprop</c1><c2>1</c2><c2 m="2">3</c2></row>'));
insert into test(recid, xmlrecord) 
values(4, XMLPARSE (DOCUMENT '<?xml
version="1.0"?><row><c1>Bprop</c1><c2>1</c2><c2 m="2">4</c2></row>'));
insert into test(recid, xmlrecord) 
values(5, XMLPARSE (DOCUMENT '<?xml
version="1.0"?><row><c1>Dprop</c1><c2>1</c2><c2 m="2">A</c2></row>'));
insert into test(recid, xmlrecord) 
values(6, XMLPARSE (DOCUMENT '<?xml
version="1.0"?><row><c1>Eprop</c1><c2>1</c2><c2 m="2">D</c2></row>'));
insert into test(recid, xmlrecord) 
values(7, XMLPARSE (DOCUMENT '<?xml
version="1.0"?><row><c1>Fprop</c1><c2>1</c2><c2 m="2">test</c2></row>'));

-- Correct
SELECT xpath('/row/c1', xmlrecord) as c1, xpath('/row/c2[@m=2 or not(@m)]',
t.xmlrecord) as row, xpath('/row/c2[@m=2 or not(@m)]/text() =
''2''',t.xmlrecord) VAL FROM test t;

--Incorrect, ASCII 'A', 'D', and 'test' > ASCII '2' or UT8 values as well.
SELECT xpath('/row/c1', xmlrecord) as c1, xpath('/row/c2[@m=2 or not(@m)]',
t.xmlrecord) as row, xpath('/row/c2[@m=2 or not(@m)]/text() >=
''2''',t.xmlrecord) VAL FROM test t;
-- {<c1>Manual</c1>}  | {<c2>1</c2>,"<c2 m=\"2\">2</c2>"}    | {true}
-- {<c1>Apropos</c1>} | {<c2>1</c2>,"<c2 m=\"2\">2</c2>"}    | {true}
-- {<c1>Apropos</c1>} | {<c2>1</c2>,"<c2 m=\"2\">3</c2>"}    | {true}
-- {<c1>Apropos</c1>} | {<c2>1</c2>,"<c2 m=\"2\">A</c2>"}    | {false}
-- {<c1>Apropos</c1>} | {<c2>1</c2>,"<c2 m=\"2\">D</c2>"}    | {false}
-- {<c1>Apropos</c1>} | {<c2>1</c2>,"<c2 m=\"2\">test</c2>"} | {false}

--Correct 
SELECT xpath('/row/c1', xmlrecord) as c1, xpath('/row/c2/text()',
t.xmlrecord) as c2, xpath('/row/c2/text() = ''A''',t.xmlrecord) VAL FROM
test t WHERE cast(xpath('/row/c2/text() = ''A''',t.xmlrecord) as VARCHAR) =
'{true}';
-- {<c1>Dprop</c1>} | {1,A} | {true}
SELECT xpath('/row/c1', xmlrecord) as c1, xpath('/row/c2', t.xmlrecord) as
c2, xpath('/row/c2[@m="2"]/text() = ''A''',t.xmlrecord) VAL FROM test t;

--Incorrect, no rows selected or returns false for all rows.
SELECT xpath('/row/c1', xmlrecord) as c1, xpath('/row/c2/text()',
t.xmlrecord) as c2, xpath('/row/c2/text() >= ''A''',t.xmlrecord) VAL FROM
test t WHERE cast(xpath('/row/c2/text() >= ''A''',t.xmlrecord) as VARCHAR) =
'{true}';
SELECT xpath('/row/c1', xmlrecord) as c1, xpath('/row/c2', t.xmlrecord) as
c2, xpath('/row/c2[@m=2]/text() >= ''A''',t.xmlrecord) VAL FROM test t;
SELECT xpath('/row/c1', xmlrecord) as c1, xpath('/row/c2', t.xmlrecord) as
c2, xpath('/row/c2[@m=2]/text() >= A',t.xmlrecord) VAL FROM test t;
SELECT xpath('/row/c1', xmlrecord) as c1, xpath('/row/c2', t.xmlrecord) as
c2, xpath('/row/c2[@m="2"]/text() >= ''A''',t.xmlrecord) VAL FROM test t;
-- same as above query but xmlexists returns always true
SELECT xpath('/row/c1', xmlrecord) as c1, xpath('/row/c2', t.xmlrecord) as
c2, xpath('/row/c2[@m="2"]/text() = ''A''',t.xmlrecord) VAL,
xmlexists('/row/c2[@m="2"]/text() = ''A''' PASSING t.xmlrecord) xmlex FROM
test t;

> On 16 Jul 2019, at 13:55, PG Bug reporting form <noreply@postgresql.org> wrote:

> --Incorrect, ASCII 'A', 'D', and 'test' > ASCII '2' or UT8 values as well.
> SELECT xpath('/row/c1', xmlrecord) as c1, xpath('/row/c2[@m=2 or not(@m)]',
> t.xmlrecord) as row, xpath('/row/c2[@m=2 or not(@m)]/text() >=
> ''2''',t.xmlrecord) VAL FROM test t;
> -- {<c1>Manual</c1>}  | {<c2>1</c2>,"<c2 m=\"2\">2</c2>"}    | {true}
> -- {<c1>Apropos</c1>} | {<c2>1</c2>,"<c2 m=\"2\">2</c2>"}    | {true}
> -- {<c1>Apropos</c1>} | {<c2>1</c2>,"<c2 m=\"2\">3</c2>"}    | {true}
> -- {<c1>Apropos</c1>} | {<c2>1</c2>,"<c2 m=\"2\">A</c2>"}    | {false}
> -- {<c1>Apropos</c1>} | {<c2>1</c2>,"<c2 m=\"2\">D</c2>"}    | {false}
> -- {<c1>Apropos</c1>} | {<c2>1</c2>,"<c2 m=\"2\">test</c2>"} | {false}

Far from being an exprt in this area, but AFAIK xpath() is evaluated as XPath
1.0 and according to the specification [1] only = and != comparison are defined:

   "When neither object to be compared is a node-set and the operator is <=, <,
    >= or >, then the objects are compared by converting both objects to
    numbers and comparing the numbers according to IEEE 754."

‘A', 'D' and ‘test’ are thus converted to floating point NaN’s.

cheers ./daniel

[1] https://www.w3.org/TR/1999/REC-xpath-19991116/#booleans

> On 18 Jul 2019, at 11:48, Rick Vincent <rvincent@temenos.com> wrote:

> Thanks.  It explains why ends-with doesn't work either.  I don't suppose XPath 2.0 version is supported or will be
supported?

I haven’t heard of anyone working on that, and it seems unlikely to happen
within the near future as few, if any, libraries support XPath 2.0 (or later).

cheers ./daniel

Hi Daniel,

Thanks for your reply.  I believe all of the major database vendors support it as we implement them all (Oracle, DB2,
SQLServer)...orat least their operators >,>=, etc work with XMLExists and do text compares and don't convert any text
todecimal.  Do you have an answer why XMLExists always seems to be returning true, ie. the last query I sent?
 

T24=# SELECT xpath('/row/c1', xmlrecord) as c1, xpath('/row/c2', t.xmlrecord) as c2, xpath('/row/c2[@m="2"]/text() =
''A''',t.xmlrecord)VAL, xmlexists('/row/c2[@m="2"]/text() = ''A''' PASSING t.xmlrecord) xmlex FROM test t;
 
         c1         |                  c2                  |   val   | xmlex
--------------------+--------------------------------------+---------+-------
 {<c1>Manual</c1>}  | {<c2>1</c2>,"<c2 m=\"2\">2</c2>"}    | {false} | t
 {<c1>Apropos</c1>} | {<c2>1</c2>,"<c2 m=\"2\">2</c2>"}    | {false} | t
 {<c1>Bprop</c1>}   | {<c2>1</c2>,"<c2 m=\"2\">3</c2>"}    | {false} | t
 {<c1>Bprop</c1>}   | {<c2>1</c2>,"<c2 m=\"2\">4</c2>"}    | {false} | t
 {<c1>Dprop</c1>}   | {<c2>1</c2>,"<c2 m=\"2\">A</c2>"}    | {true}  | t
 {<c1>Eprop</c1>}   | {<c2>1</c2>,"<c2 m=\"2\">D</c2>"}    | {false} | t
 {<c1>Fprop</c1>}   | {<c2>1</c2>,"<c2 m=\"2\">test</c2>"} | {false} | t
(7 rows)

Big thanks,
Rick
-----Original Message-----
From: Daniel Gustafsson <daniel@yesql.se>
Sent: Thursday, July 18, 2019 3:06 PM
To: Rick Vincent <rvincent@temenos.com>
Cc: Herve Aubert <haubert@temenos.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #15908: Xpath operations fail

> On 18 Jul 2019, at 11:48, Rick Vincent <rvincent@temenos.com> wrote:

> Thanks.  It explains why ends-with doesn't work either.  I don't suppose XPath 2.0 version is supported or will be
supported?

I haven’t heard of anyone working on that, and it seems unlikely to happen within the near future as few, if any,
librariessupport XPath 2.0 (or later).
 

cheers ./daniel

The information in this e-mail and any attachments is confidential and may be legally privileged. It is intended solely
forthe addressee or addressees. Any use or disclosure of the contents of this e-mail/attachments by a not intended
recipientis unauthorized and may be unlawful. If you have received this e-mail in error please notify the sender.
Pleasenote that any views or opinions presented in this e-mail are solely those of the author and do not necessarily
representthose of TEMENOS. We recommend that you check this e-mail and any attachments against viruses. TEMENOS accepts
noliability for any damage caused by any malicious code or virus transmitted by this e-mail.
 

Pavel Stehule <pavel.stehule@gmail.com> writes:
> čt 18. 7. 2019 v 15:06 odesílatel Daniel Gustafsson <daniel@yesql.se>
> napsal:
>> I haven’t heard of anyone working on that, and it seems unlikely to happen
>> within the near future as few, if any, libraries support XPath 2.0 (or
>> later).

> Only XPath 1.0 is supported by libxml2. And this library is in permanent
> feature freeze mode :-(. Unfortunately, there is not any other library with
> friendly licence available for C language.

Not only that, but libxml2 is a rich source of security bugs.  I wish
we could get rid of that dependency altogether.  Unfortunately,
reimplementing our own xml and xpath functionality from scratch
seems like a large project :-(.  I don't think there's anybody
with sufficient time and interest to do it.

            regards, tom lane

The information in this e-mail and any attachments is confidential and may be legally privileged. It is intended solely for the addressee or addressees. Any use or disclosure of the contents of this e-mail/attachments by a not intended recipient is unauthorized and may be unlawful. If you have received this e-mail in error please notify the sender. Please note that any views or opinions presented in this e-mail are solely those of the author and do not necessarily represent those of TEMENOS. We recommend that you check this e-mail and any attachments against viruses. TEMENOS accepts no liability for any damage caused by any malicious code or virus transmitted by this e-mail.