From: | Thiago Silva <thiago(dot)silva(at)kdemail(dot)net> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | XML2 module: odd query results |
Date: | 2006-07-19 17:03:26 |
Message-ID: | 200607191403.26825.thiago.silva@kdemail.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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</systemtag><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</systemtag><ownerid/></tag><name>test</name><description>testdesc</description><systemtag>1</systemtag><ownerid/>
(1 registro)
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Hammond | 2006-07-19 18:14:13 | double insert on inherited table with where constraint based on sequence |
Previous Message | John Lyssy | 2006-07-18 13:55:03 | BUG #2538: Hang on insert/select |