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. You can look at the EXPLAIN output to see the difference.
-- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers