Thread: BUG #1335: Wrong sort result in union queries

The following bug has been logged online:

Bug reference:      1335
Logged by:          snaky

Email address:      snaky@ulstu.ru

PostgreSQL version: 8.0 Beta

Operating system:   Windows 2003/XP

Description:        Wrong sort result in union queries

Details:

sorry for my english.

Query:
select 2 union select 1

Result:
1
2

Why? I think the result must be like this:
2
1

Why PostgreSQL sort union queries by first column by default?
Certainly, I understand that I can write general "order by" in the end of
query. However, in this case, I can't make queries with "manual" row ording.
And what is more, this query does not work properly:

(select * from (select 1, 2 union select 4, 3) as a
order by 2 desc)
union
select 1, 1

Result must be like this:
4, 3
1, 2
1, 1

but real result is:
1, 1
1, 2
4, 3


Full version info:
PostgreSQL 8.0.0beta1 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC)
3.2.3 (mingw special 20030504-1)

On Wed, 1 Dec 2004, PostgreSQL Bugs List wrote:

> Query:
> select 2 union select 1
>
> Result:
> 1
> 2
>
> Why? I think the result must be like this:
> 2
> 1

If you don't specify an order by (at the top level) the output has no
defined order by SQL, so both orders are valid.

> Why PostgreSQL sort union queries by first column by default?
> Certainly, I understand that I can write general "order by" in the end of
> query. However, in this case, I can't make queries with "manual" row ording.

Union isn't a tool which gives you that ability. Union All is closer, but
still doesn't guarantee an order.

The only one I can think of is to assign weights to the rows as you're
going and ordering by that at the top level.

> (select * from (select 1, 2 union select 4, 3) as a
> order by 2 desc)
> union
> select 1, 1
>
> Result must be like this:
> 4, 3
> 1, 2
> 1, 1
>
> but real result is:
> 1, 1
> 1, 2
> 4, 3

This one is also okay. The order by in one wing does not control the
output of the union. I believe supporting it is an extension in any case
(at least SQL92 seems to make it illegal) and is probably meant for
interaction with limit.

Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> Union isn't a tool which gives you that ability. Union All is closer, but
> still doesn't guarantee an order.

The current implementation of UNION ALL will in fact act that way (just
append the individual query results together), but if for some reason we
decide to change it later, we'll be within the spec to do so.  (I can't
imagine a reason to change it though...)

> This one is also okay. The order by in one wing does not control the
> output of the union. I believe supporting it is an extension in any case
> (at least SQL92 seems to make it illegal)

Yes.  SQL92 believes strongly that row order is not significant, except
at the top level output of a query with ORDER BY ...

            regards, tom lane