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