Hi,
Recently, I had some problems with queries using the XML2 module. I was abl=
e=20
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=3D"test"]');
DROP TABLE tag CASCADE;
CREATE TABLE tag ( id SERIAL PRIMARY KEY, data TEXT );
INSERT INTO tag ( data ) VALUES ( '<?xml version=3D"1.0"=20
encoding=3D"UTF-8"?><tag><name>test</name><description>testdesc</descriptio=
n><systemtag>1</systemtag><ownerid></ownerid></tag>' );
SELECT id, xpath_nodeset(data, '/*[/tag/name=3D"test" and /tag/systemtag=3D=
1]') AS=20
data FROM tag WHERE xpath_bool(data, '/*[/tag/name=3D"test"=20
and /tag/systemtag=3D1]/..');



Executing them about 10/20 times in the console (sometimes, way more) shows=
2=20
different results for the last SELECT command. And, AFAIK, there should be=
=20
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=20
brazilian locale, sorry) in a DB called "test":

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

test=3D> DROP TABLE tag CASCADE;
DROP TABLE
test=3D> CREATE TABLE tag ( id SERIAL PRIMARY KEY, data TEXT );
NOTA: CREATE TABLE criar=E1 sequ=EAncia impl=EDcita "tag_id_seq" para colu=
na=20
serial "tag.id"
NOTA: CREATE TABLE / PRIMARY KEY criar=E1 =EDndice impl=EDcito "tag_pkey" =
na=20
tabela "tag"
CREATE TABLE
test=3D> INSERT INTO tag ( data ) VALUES ( 'anything' );
INSERT 0 1
test=3D> SELECT id, data FROM tag WHERE=20
xpath_bool(data, '/*[/tag/name=3D"test"]');
id | data
----+------
(0 registros)

test=3D> DROP TABLE tag CASCADE;
DROP TABLE
test=3D> CREATE TABLE tag ( id SERIAL PRIMARY KEY, data TEXT );
NOTA: CREATE TABLE criar=E1 sequ=EAncia impl=EDcita "tag_id_seq" para colu=
na=20
serial "tag.id"
NOTA: CREATE TABLE / PRIMARY KEY criar=E1 =EDndice impl=EDcito "tag_pkey" =
na=20
tabela "tag"
CREATE TABLE
test=3D> INSERT INTO tag ( data ) VALUES ( '<?xml version=3D"1.0"=20
encoding=3D"UTF-8"?><tag><name>test</name><description>testdesc</descriptio=
n><systemtag>1</systemtag><ownerid></ownerid></tag>' );
INSERT 0 1
test=3D> SELECT id, xpath_nodeset(data, '/*[/tag/name=3D"test"=20
and /tag/systemtag=3D1]') AS data FROM tag WHERE=20
xpath_bool(data, '/*[/tag/name=3D"test" and /tag/systemtag=3D1]/..');
id | data
----+----------------------------------------------------------------------=
-----------------------------
1 |=20
<tag><name>test</name><description>testdesc</description><systemtag>1</syst=
emtag><ownerid/></tag>
(1 registro)


++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++
test=3D> DROP TABLE tag CASCADE;
DROP TABLE
test=3D> CREATE TABLE tag ( id SERIAL PRIMARY KEY, data TEXT );
NOTA: CREATE TABLE criar=E1 sequ=EAncia impl=EDcita "tag_id_seq" para colu=
na=20
serial "tag.id"
NOTA: CREATE TABLE / PRIMARY KEY criar=E1 =EDndice impl=EDcito "tag_pkey" =
na=20
tabela "tag"
CREATE TABLE
test=3D> INSERT INTO tag ( data ) VALUES ( 'anything' );
INSERT 0 1
test=3D> SELECT id, data FROM tag WHERE=20
xpath_bool(data, '/*[/tag/name=3D"test"]');
id | data
----+------
(0 registros)

test=3D> DROP TABLE tag CASCADE;
DROP TABLE
test=3D> CREATE TABLE tag ( id SERIAL PRIMARY KEY, data TEXT );
NOTA: CREATE TABLE criar=E1 sequ=EAncia impl=EDcita "tag_id_seq" para colu=
na=20
serial "tag.id"
NOTA: CREATE TABLE / PRIMARY KEY criar=E1 =EDndice impl=EDcito "tag_pkey" =
na=20
tabela "tag"
CREATE TABLE
test=3D> INSERT INTO tag ( data ) VALUES ( '<?xml version=3D"1.0"=20
encoding=3D"UTF-8"?><tag><name>test</name><description>testdesc</descriptio=
n><systemtag>1</systemtag><ownerid></ownerid></tag>' );
INSERT 0 1
test=3D> SELECT id, xpath_nodeset(data, '/*[/tag/name=3D"test"=20
and /tag/systemtag=3D1]') AS data FROM tag WHERE=20
xpath_bool(data, '/*[/tag/name=3D"test" and /tag/systemtag=3D1]/..');
id |=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= 20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=2 0=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=2 0=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=2 0
data=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= 20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=2 0=20=20
----+----------------------------------------------------------------------=
---------------------------------------------------------------------------=
----------------------------------------
1 |=20
<tag><name>test</name><description>testdesc</description><systemtag>1</syst=
emtag><ownerid/></tag><name>test</name><description>testdesc</description><=
systemtag>1</systemtag><ownerid/>
(1 registro)



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match