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

On Sat, Apr 18, 2009 at 11:19 PM, Grzegorz Jaskiewicz
<gj(at)pointblue(dot)com(dot)pl> wrote:
> This is a really funny one, because people naturally expect UNION [ALL] to
> stay in the same order. Unlike the table, order here cannot change by
> inserts/updates, etc.
> I am sure many, even well experienced will stumble upon that one.

There is a misunderstanding here. UNION has *never* preserved the
order of the subqueries before. In the OP's query it was *not*
preserving the order. It was a coincidence that the order the subquery
was in was sorted on the first field and since UNION resorted the
whole result set by all the fields in order that meant it was in order
by the first field.

> Me is guessing, that UNION [ALL] performance just had to be improved for
> CTEs ? Or was it something completely separate.

Hash aggregates were new relative to set operations which have been
around a very long time. They didn't take advantage of the new code
but it was always fairly obvious that they should eventually have been
changed to. I think it came along with fixing DISTINCT to use hash
aggregates which was a similar situation.

UNION ALL should still preserve the order of the subqueries. It just
returns all the rows of each subquery one after the other with no
other work.

--
greg