Aggregation ordering with GROUP BY

Started by Thomas Schoenover 20 years ago2 messagesgeneral
Jump to latest
#1Thomas Schoen
t.schoen@vitrado.de

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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Thomas Schoen (#1)
Re: Aggregation ordering with GROUP BY

Thomas Schoen <t.schoen@vitrado.de> writes:

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.)

No, the spec doesn't promise anything, and so we don't either. In
practice though I find it hard to visualize why the implementation would
not process all the aggregates in parallel.

regards, tom lane