Postgres Pro
Downloads
Home   >   mailing lists

Thread: xmlagg doesn't honor LIMIT?

xmlagg doesn't honor LIMIT?

From
Peter Kroon
Date:
Is anyone able to reproduce?
When I run the query below all 5 rows are returned instead of 2.
Or is this the default behaviour..

"PostgreSQL 9.2.4 on i686-pc-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52), 32-bit"

DROP TABLE IF EXISTS __pg_test_table CASCADE;
CREATE TABLE __pg_test_table(
id serial,
some_value int,
__rel int
);
INSERT INTO __pg_test_table(some_value,__rel)VALUES(1,5);
INSERT INTO __pg_test_table(some_value,__rel)VALUES(2,5);
INSERT INTO __pg_test_table(some_value,__rel)VALUES(3,5);
INSERT INTO __pg_test_table(some_value,__rel)VALUES(4,5);
INSERT INTO __pg_test_table(some_value,__rel)VALUES(5,5);
INSERT INTO __pg_test_table(some_value,__rel)VALUES(6,6);
INSERT INTO __pg_test_table(some_value,__rel)VALUES(7,6);
INSERT INTO __pg_test_table(some_value,__rel)VALUES(8,6);
INSERT INTO __pg_test_table(some_value,__rel)VALUES(9,6);
INSERT INTO __pg_test_table(some_value,__rel)VALUES(10,6);


SELECT
xmlagg(
xmlconcat(
xmlelement(name test_element,
xmlattributes(
0 AS m 
),
xmlforest(
dh.id AS i
,dh.some_value AS sv
)--xmlforest
)--test_element
)--xmlconcat
ORDER BY id DESC
)--xmlagg
FROM __pg_test_table AS dh
WHERE dh.__rel=5 LIMIT 2 --OFFSET 10;

Re: xmlagg doesn't honor LIMIT?

From
Albe Laurenz
Date:
Peter Kroon wrote:
> Is anyone able to reproduce?
> When I run the query below all 5 rows are returned instead of 2.
> Or is this the default behaviour..

> SELECT
> xmlagg(
[...]
> )--xmlagg
> FROM __pg_test_table AS dh
> WHERE dh.__rel=5 LIMIT 2 --OFFSET 10;

According to the documentation, that query should return
exactly one row since xmlagg is an aggregate.

So the LIMIT 2 won't do anything to the result.

You can wrap your query in a
SELECT count(*) FROM (SELECT ...) AS dummy;
to see how many rows you got.

Yours,
Laurenz Albe

Re: xmlagg doesn't honor LIMIT?

From
Peter Kroon
Date:
This is how I solved it:

SELECT
xmlagg(
xmlconcat(
xmlelement(name test_element,
xmlforest(
ff.d AS a
)--xmlforest
)
)--xmlconcat
)--xmlagg

FROM (
SELECT
--xmlagg(
xmlconcat(
xmlelement(name test_element,
xmlattributes(
0 AS m 
),
xmlforest(
dh.id AS i
,dh.some_value AS sv
)--xmlforest
)--test_element
) AS d 
--)
FROM __pg_test_table AS dh
WHERE dh.__rel=5 LIMIT 2 --OFFSET 10;
) AS ff;


2013/11/26 Albe Laurenz <laurenz.albe@wien.gv.at>
Peter Kroon wrote:
> Is anyone able to reproduce?
> When I run the query below all 5 rows are returned instead of 2.
> Or is this the default behaviour..

> SELECT
> xmlagg(
[...]
> )--xmlagg
> FROM __pg_test_table AS dh
> WHERE dh.__rel=5 LIMIT 2 --OFFSET 10;

According to the documentation, that query should return
exactly one row since xmlagg is an aggregate.

So the LIMIT 2 won't do anything to the result.

You can wrap your query in a
SELECT count(*) FROM (SELECT ...) AS dummy;
to see how many rows you got.

Yours,
Laurenz Albe