Hi,
my problem is best descibed giving an example:
Assume you wrote a simple aggregate function called "agg_array_append"
wich appends integer values to an array.
Further assume you have a table "foo" with three integer fields "a", "b"
and "c".
test=# select * from foo;
a | b | c
---+---+---
1 | 1 | 1
1 | 2 | 2
2 | 3 | 3
2 | 4 | 4
(4 rows)
If you do the following:
test=# select a, agg_array_append(b), agg_array_append(c) from foo group
by a;
a | agg_array_append | agg_array_append
---+------------------+------------------
1 | {1,2} | {1,2}
2 | {4,3} | {4,3}
(2 rows)
The fields "b" and "c" are aggregated in the same order for each "a".
If you have some traffic on your table like:
update foo set c = 12 where a = 1 and b = 2;
update foo set c = 13 where a = 2 and b = 3;
test=# select * from foo2;
a | b | c
---+---+----
1 | 1 | 1
2 | 4 | 4
1 | 2 | 12
2 | 3 | 13
(4 rows)
You still get a result in which the two aggregates are ordered in the
same way:
test=# select a, agg_array_append(b), agg_array_append(c) from foo group
by a;
a | agg_array_append | agg_array_append
---+------------------+------------------
2 | {4,3} | {4,13}
1 | {1,2} | {1,12}
(2 rows)
Array-Position 1: 4 and 4 (from the same aggregated row)
Array-Position 2: 3 and 13 (from the same aggregated row)
One might assume there is a chance to get a result like:
a | agg_array_append | agg_array_append
---+------------------+------------------
2 | {4,3} | {13,4}
1 | {1,2} | {12,1}
Or even worse:
a | agg_array_append | agg_array_append
---+------------------+------------------
2 | {4,3} | {13,4}
1 | {1,2} | {1,12}
Now, finally, my questions are:
- Is there any guaranty, that two aggregates in a GROUP BY statement are
aggregated in the same order?
- If yes: is there any guaranty the behaviour won't change in future
versions of PG. (I suppose SQL-standard doesn't make any statement about
how the behaviour should be here.)
Thanks in advance,
Thomas
