BUG #1335: Wrong sort result in union queries

PostgreSQL Bugs List <pgsql-bugs@postgresql.org> wrote on 01.12.2004,
13:09:23:

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)

This is not a bug.

Your report says "wrong sort result". You haven't asked for the query to
be sorted, so how can the order by wrong?

If you do not specify an ORDER BY clause on your SQL, then the ordering
of rows is not specified. As a result, PostgreSQL is perfectly entitled
to return this result set to you, or any other ordering.

UNION runs an extra step to produce a DISTINCT operation between the two
queries. The natural by-product of that is sorted output.

If you wish, you may use UNION ALL, which avoids the DISTINCT step, but
this would not guarantee that the ordering would be anything at all, as
before.

Bottom line: If you care about the ordering of rows returned by a query,
you should use ORDER BY to specify the desired result. If you don't
care - why worry?

Best Regards, Simon Riggs

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