BUG #15908: Xpath operations fail - Mailing list pgsql-bugs

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;