The following bug has been logged online:

Bug reference: 2541
Logged by: Thiago Silva
Email address: thiago.silva (AT) kdemail (DOT) net
PostgreSQL version: 8.1
Operating system: Debian GNU/Linux
Description: XML2 module: odd query results
Details:

Hi,
Recently, I had some problems with queries using the XML2 module. I was able

to reproduce the odd results using the following commands:



DROP TABLE tag CASCADE;
CREATE TABLE tag ( id SERIAL PRIMARY KEY, data TEXT );
INSERT INTO tag ( data ) VALUES ( 'anything' );
SELECT id, data FROM tag WHERE xpath_bool(data, '/*[/tag/name="test"]');
DROP TABLE tag CASCADE;
CREATE TABLE tag ( id SERIAL PRIMARY KEY, data TEXT );
INSERT INTO tag ( data ) VALUES ( '<?xml version="1.0"
encoding="UTF-8"?><tag><name>test</name><description>testdesc</description><
systemtag>1</systemtag><ownerid></ownerid></tag>' );
SELECT id, xpath_nodeset(data, '/*[/tag/name="test" and /tag/systemtag=1]')
AS
data FROM tag WHERE xpath_bool(data, '/*[/tag/name="test"
and /tag/systemtag=1]/..');



Executing them about 10/20 times in the console (sometimes, way more) shows
2
different results for the last SELECT command. And, AFAIK, there should be
only one result, no matter how many times they are executed.

I'm using postgresql 8.1 (GNU/Linux Debian testing).

Thiago Silva

PS: The following is a copy/paste of the console for both results (using
brazilian locale, sorry) in a DB called "test":

++++++++++++++++++++++++++++++++++++++++++++++++++ ++++

test=> DROP TABLE tag CASCADE;
DROP TABLE
test=> CREATE TABLE tag ( id SERIAL PRIMARY KEY, data TEXT );
NOTA: Â*CREATE TABLE criará sequência implÃ*cita "tag_id_seq" para coluna

serial "tag.id"
NOTA: Â*CREATE TABLE / PRIMARY KEY criará Ã*ndice implÃ*cito "tag_pkey" na

tabela "tag"
CREATE TABLE
test=> INSERT INTO tag ( data ) VALUES ( 'anything' );
INSERT 0 1
test=> SELECT id, data FROM tag WHERE
xpath_bool(data, '/*[/tag/name="test"]');
Â*id | data
----+------
(0 registros)

test=> DROP TABLE tag CASCADE;
DROP TABLE
test=> CREATE TABLE tag ( id SERIAL PRIMARY KEY, data TEXT );
NOTA: Â*CREATE TABLE criará sequência implÃ*cita "tag_id_seq" para coluna

serial "tag.id"
NOTA: Â*CREATE TABLE / PRIMARY KEY criará Ã*ndice implÃ*cito "tag_pkey" na

tabela "tag"
CREATE TABLE
test=> INSERT INTO tag ( data ) VALUES ( '<?xml version="1.0"
encoding="UTF-8"?><tag><name>test</name><description>testdesc</description><
systemtag>1</systemtag><ownerid></ownerid></tag>' );
INSERT 0 1
test=> SELECT id, xpath_nodeset(data, '/*[/tag/name="test"
and /tag/systemtag=1]') AS data FROM tag WHERE
xpath_bool(data, '/*[/tag/name="test" and /tag/systemtag=1]/..');
Â*id | Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â*
data
----+-----------------------------------------------------------------------
----------------------------
Â* 1 |
<tag><name>test</name><description>testdesc</description><systemtag>1</syste
mtag><ownerid/></tag>
(1 registro)


++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++
test=> DROP TABLE tag CASCADE;
DROP TABLE
test=> CREATE TABLE tag ( id SERIAL PRIMARY KEY, data TEXT );
NOTA: Â*CREATE TABLE criará sequência implÃ*cita "tag_id_seq" para coluna

serial "tag.id"
NOTA: Â*CREATE TABLE / PRIMARY KEY criará Ã*ndice implÃ*cito "tag_pkey" na

tabela "tag"
CREATE TABLE
test=> INSERT INTO tag ( data ) VALUES ( 'anything' );
INSERT 0 1
test=> SELECT id, data FROM tag WHERE
xpath_bool(data, '/*[/tag/name="test"]');
Â*id | data
----+------
(0 registros)

test=> DROP TABLE tag CASCADE;
DROP TABLE
test=> CREATE TABLE tag ( id SERIAL PRIMARY KEY, data TEXT );
NOTA: Â*CREATE TABLE criará sequência implÃ*cita "tag_id_seq" para coluna

serial "tag.id"
NOTA: Â*CREATE TABLE / PRIMARY KEY criará Ã*ndice implÃ*cito "tag_pkey" na

tabela "tag"
CREATE TABLE
test=> INSERT INTO tag ( data ) VALUES ( '<?xml version="1.0"
encoding="UTF-8"?><tag><name>test</name><description>testdesc</description><
systemtag>1</systemtag><ownerid></ownerid></tag>' );
INSERT 0 1
test=> SELECT id, xpath_nodeset(data, '/*[/tag/name="test"
and /tag/systemtag=1]') AS data FROM tag WHERE
xpath_bool(data, '/*[/tag/name="test" and /tag/systemtag=1]/..');
Â*id | Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â*
Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â*
data Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â*
----+-----------------------------------------------------------------------
----------------------------------------------------------------------------
--------------------------------------
Â* 1 |
<tag><name>test</name><description>testdesc</description><systemtag>1</syste
mtag><ownerid/></tag><name>test</name><description>testdesc</description><sy
stemtag>1</systemtag><ownerid/>
(1 registro)

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Hi,

That is strange behaviour - in summary, sometimes after running the
sample statements you get the result duplicated (as it looks in your
example), sometimes not?

I will see if I can reproduce it here and work out how that could happen.

Regards

John



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster