Обсуждение: Aggregation ordering with GROUP BY

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

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