BUG #15908: Xpath operations fail

Started by PG Bug reporting formover 6 years ago8 messagesbugs

noreply@postgresql.org

over 6 years ago

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;

Daniel Gustafsson

daniel@yesql.se

over 6 years ago

In reply to: PG Bug reporting form (#1)

Re: BUG #15908: Xpath operations fail

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]https://www.w3.org/TR/1999/REC-xpath-19991116/#booleans 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

Daniel Gustafsson

daniel@yesql.se

over 6 years ago

In reply to: PG Bug reporting form (#1)

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, libraries support XPath 2.0 (or later).

cheers ./daniel

Import Notes

Reply to msg id not found: MN2PR04MB5806415551ABA3250D0308AAC1C80@MN2PR04MB5806.namprd04.prod.outlook.com

Rick Vincent

rvincent@temenos.com

over 6 years ago

In reply to: Daniel Gustafsson (#3)

RE: BUG #15908: Xpath operations fail

Hi Daniel,

Thanks for your reply. I believe all of the major database vendors support it as we implement them all (Oracle, DB2, SQLServer)...or at least their operators >,>=, etc work with XMLExists and do text compares and don't convert any text to decimal. 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, libraries support 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 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.

Pavel Stehule

pavel.stehule@gmail.com

over 6 years ago

In reply to: Daniel Gustafsson (#3)

Re: BUG #15908: Xpath operations fail

čt 18. 7. 2019 v 15:06 odesílatel Daniel Gustafsson <daniel@yesql.se>
napsal:

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).

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.

Regards

Pavel

Show quoted text

cheers ./daniel

Tom Lane

tgl@sss.pgh.pa.us

over 6 years ago

In reply to: Pavel Stehule (#5)

Re: BUG #15908: Xpath operations fail

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

Pavel Stehule

pavel.stehule@gmail.com

over 6 years ago

In reply to: Tom Lane (#6)

Re: BUG #15908: Xpath operations fail

čt 18. 7. 2019 v 16:22 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:

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.

Maybe some C++ or Java library with good licence can be ported to C for
usage in Postgres.

But it is lot of work.

Pavel

Show quoted text

regards, tom lane

Rick Vincent

rvincent@temenos.com

over 6 years ago

In reply to: Pavel Stehule (#7)

RE: BUG #15908: Xpath operations fail

Hi all,

Thank you for your responses.

Best,
Rick

From: Pavel Stehule <pavel.stehule@gmail.com>
Sent: Thursday, July 18, 2019 4:27 PM
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Daniel Gustafsson <daniel@yesql.se>; Rick Vincent <rvincent@temenos.com>; Herve Aubert <haubert@temenos.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #15908: Xpath operations fail

čt 18. 7. 2019 v 16:22 odesílatel Tom Lane <tgl@sss.pgh.pa.us<mailto:tgl@sss.pgh.pa.us>> napsal:
Pavel Stehule <pavel.stehule@gmail.com<mailto:pavel.stehule@gmail.com>> writes:

čt 18. 7. 2019 v 15:06 odesílatel Daniel Gustafsson <daniel@yesql.se<mailto: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.

Maybe some C++ or Java library with good licence can be ported to C for usage in Postgres.

But it is lot of work.

Pavel

regards, tom lane