> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Tom Lane
> Sent: Wednesday, November 08, 2006 7:05 AM
> To: redhog
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Stable sort?
>
> "redhog" <redhog@redhog.org> writes:
> > My question was if the order of two
> > elements whose internal order is not affected by the current
ordering
> > clause, still may change places due to technicalities.
>
> Postgres usually sorts using qsort(), which (on most platforms) is not
> stable in that sense.
This is (of course) what we would expect from any SQL database.
From ISO/IEC ISO/IEC 9075-2:1999 (E) Section 4.29 Cursors on page 71, we
have this:
"A cursor in the open state identifies a table, an ordering of the rows
of that table, and a position relative to that ordering. If the <declare
cursor> does not contain an <order by clause>, or contains an <order by
clause> that does not specify the order of the rows completely, then the
rows of the table have an order that is defined only to the extent that
the <order by clause> specifies an order and is otherwise
implementation-dependent.
When the ordering of a cursor is not defined by an <order by clause>,
the relative position of two rows is implementation-dependent. When the
ordering of a cursor is partially determined by an <order by clause>,
then the relative positions of two rows are determined only by the
<order by clause>; if the two rows have equal values for the purpose of
evaluating the <order by clause>, then their relative positions are
implementation-dependent."
This is talking about cursors specifically, but the same ordering
principles would obviously hold throughout.
The standard does not prevent the use of a stable sort, but it also does
not require it.
Obviously, if you order by all the result columns, then the orderings
will always be identical (though this would in general be a bad idea).