Aggregation ordering with GROUP BY

From: Thomas Schoen <t(dot)schoen(at)vitrado(dot)de>
To: pgsql-general(at)postgresql(dot)org
Subject: Aggregation ordering with GROUP BY
Date: 2005-08-10 17:32:57
Message-ID: 42FA3A49.3050408@vitrado.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Hrishikesh Deshmukh 2005-08-10 17:33:19 Suse9.3
Previous Message Tino Wildenhain 2005-08-10 17:30:22 Re: World-wide Stop Order on PERL,Python,Java::->> Use