Re: 8.4b1: Query returning results in different order to 8.3

2009/4/19 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>

> Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
> > Ian Barwick wrote:
> >> Note I'm not sure whether this is a bug, or whether the assumption
> >> made for the original query (that the row order returned by the
> >> subquery would be carried over to the main part of the query) is
> >> incorrect but just happened to work as expected pre-8.4.
>
> > The latter. Without an ORDER BY (at the outermost level), the order of
> > the result is not well defined. Before 8.4, UNION was always performed
> > by a Sort + Unique, which explains why the output is always sorted in
> > previous releases. 8.4 knows how to perform it with a Hash Aggregate,
> > which doesn't yield sorted output.
>
> This is mentioned in the release notes, but I suppose we'd better
> promote it to the "observe the following incompatibilities" list...
>

Thanks for clarifying that. The relevant section in the release notes (which
I managed to miss) is this:

http://www.postgresql.org/docs/8.4/static/release-8-4.html#AEN93685

It would certainly be worth an explicit mention as I imagine the previous
behaviour has been consistent enough for queries to have come to rely on
it.

Regards

Ian Barwick