Nested xmlagg doesn't give a result 9.2.3

Discussion:

(too old to reply)

Peter Kroon

2013-02-19 00:18:41 UTC

Query:

SELECT
xmlelement(name s_a,
xmlagg(
xmlelement(name s_b,
xmlattributes(
sq.id AS i
),
(SELECT
xmlagg(
xmlelement(name s_c,
xmlattributes(
su.id AS i
)
)
ORDER BY table_id,id
)
FROM __table_to_table AS su WHERE su.table_id=sq.id
)
)
)
)
FROM __table AS sq

No result given in: "PostgreSQL 9.2.3 on x86_64-unknown-linux-gnu, compiled
by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52), 64-bit"
A result is given in: "PostgreSQL 9.2.1, compiled by Visual C++ build 1600,
32-bit"

Both installations tested with same DB.

Michael Paquier

2013-02-19 01:03:51 UTC

Permalink

Hi,

With your query and such tables:
create table __table (id int);
insert into __table values (1),(2);
create table __table_to_table (id int, table_id int);
insert into __table_to_table values (1, 1),(2, 2);

I am getting consistent results with 9.2.1 and 9.2.3 on my Linux box, hence:
xmlelement
-----------------------------------------------------------------------
<s_a><s_b i="1"><s_c i="1"/></s_b><s_b i="2"><s_c i="2"/></s_b></s_a>
(1 row)

Could you be more explicit about the result you expect and the output
differences you see between those versions?
Regards,

--
Michael

Peter Kroon

2013-02-19 07:59:25 UTC

Permalink

Hi Michael,

This should have been the expected result on both machines:
-----------------------------------------------------------------------
<s_a><s_b i="1"><s_c i="1"/></s_b><s_b i="2"><s_c i="2"/></s_b></s_a>
(1 row)

I've just rebooted "PostgreSQL 9.2.3 on x86_64-unknown-linux-gnu, compiled
by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52), 64-bit" and I'm getting the
expected result.

Thanks for helping.

Best,

Peter Kroon

Post by Michael Paquier
Hi,
create table __table (id int);
insert into __table values (1),(2);
create table __table_to_table (id int, table_id int);
insert into __table_to_table values (1, 1),(2, 2);
xmlelement
-----------------------------------------------------------------------
<s_a><s_b i="1"><s_c i="1"/></s_b><s_b i="2"><s_c i="2"/></s_b></s_a>
(1 row)
Could you be more explicit about the result you expect and the output
differences you see between those versions?
Regards,
--
Michael

Peter Kroon

2013-02-19 08:42:01 UTC

Permalink

Mm..
The problem only occurs on a specific table in my DB.

--__my_table has 512 rows

SELECT * FROM __my_table; --gives me result

SELECT
--xmlagg(
xmlelement(name el_name,
id
)
--)
FROM __my_table;--gives me result

When un-commenting the xmlagg I get no result. otherwise the output will
look like:
"<el_name>1</el_name>"
"<el_name>2</el_name>"
"<el_name>3</el_name>"
etc....

Post by Peter Kroon
Hi Michael,
-----------------------------------------------------------------------
<s_a><s_b i="1"><s_c i="1"/></s_b><s_b i="2"><s_c i="2"/></s_b></s_a>
(1 row)
I've just rebooted "PostgreSQL 9.2.3 on x86_64-unknown-linux-gnu,
compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52), 64-bit" and I'm
getting the expected result.
Thanks for helping.
Best,
Peter Kroon

Peter Kroon

2013-02-19 08:50:25 UTC

Permalink

Also no result with FROM __my_table LIMIT 1;

Post by Peter Kroon
Mm..
The problem only occurs on a specific table in my DB.
--__my_table has 512 rows
SELECT * FROM __my_table; --gives me result
SELECT
--xmlagg(
xmlelement(name el_name,
id
)
--)
FROM __my_table;--gives me result
When un-commenting the xmlagg I get no result. otherwise the output will
"<el_name>1</el_name>"
"<el_name>2</el_name>"
"<el_name>3</el_name>"
etc....

Michael Paquier

2013-02-19 11:27:28 UTC

Permalink

Post by Peter Kroon
Also no result with FROM __my_table LIMIT 1;

I'm having correct results with PG 9.2 by using either xmlagg or xmlelement.
For example:
postgres=# SELECT xmlelement(name el_name, id) FROM __table LIMIT 1;
xmlelement
----------------------
<el_name>1</el_name>
Or:
postgres=# SELECT xmlagg(xmlelement(name el_name, id)) FROM __table;
xmlagg
------------------------------------------
<el_name>1</el_name><el_name>2</el_name>
(1 row)

Btw, such simple tests would have failed on the buildfarm for regression
xml.sql, so this looks to be an error in your environment.
Don't you have for example problems with the client application you use?

--
Michael

Peter Kroon

2013-02-19 11:35:36 UTC

Permalink

Post by Michael Paquier
Don't you have for example problems with the client application you use?

Yes, with 1 table only. I'm not getting any results.
When I'm on the sql machine via localhost or 192.168.1.100 I'm getting
results.

Post by Michael Paquier

Post by Peter Kroon
Also no result with FROM __my_table LIMIT 1;

I'm having correct results with PG 9.2 by using either xmlagg or xmlelement.
postgres=# SELECT xmlelement(name el_name, id) FROM __table LIMIT 1;
xmlelement
----------------------
<el_name>1</el_name>
postgres=# SELECT xmlagg(xmlelement(name el_name, id)) FROM __table;
xmlagg
------------------------------------------
<el_name>1</el_name><el_name>2</el_name>
(1 row)
Btw, such simple tests would have failed on the buildfarm for regression
xml.sql, so this looks to be an error in your environment.
Don't you have for example problems with the client application you use?
--
Michael

Peter Kroon

2013-02-19 11:52:37 UTC

Permalink

Post by Peter Kroon
When I'm on the sql machine via localhost or 192.168.1.100 I'm getting

results.

I mean when I'm physically behind the machine and login via pgadmin
using localhost
or 192.168.1.100 then I get results.
When I'm on another machine and login via pgadmin(192.168.1.100) then I get
no results.
Not sure what to think of this...