Skip site navigation (1) Skip section navigation (2)

Re: Unexpected sort order.

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Jeff Davis <pgsql(at)j-davis(dot)com>
Cc: Ron Mayer <rm_pg(at)cheapcomplexdevices(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: Unexpected sort order.
Date: 2006-11-27 22:22:21
Message-ID: 24979.1164666141@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-general
I wrote:
> It looks to me like the planner thinks that order by a and order by b
> are equivalent because the expressions are equal(); hence it discards
> what it thinks is a redundant second sort step.
> ... What's the use-case for sorting by a volatile
> expression in the first place?

It may be worth pointing out that there are related gotchas without
bothering with anything as complicated as a sub-select.  Consider

	select random() from foo order by 1;
	select random() from foo order by random();

Are these the same, or not?  If you experiment you'll find out that
Postgres treats them the same --- random() is evaluated only once per
row of foo, and you get output that is sorted.  Arguably for the
second case there should be two evaluations of random() per row, and you
should get output that appears randomly ordered (because the sort key
and the output value will be uncorrelated).  If you do

	select random() from foo order by random()+1;

then you do get two evaluations and random-looking output.

I'd be the first to admit that these various behaviors "just grew"
rather than being intentionally designed; no one has been thinking
about volatility in sort keys.  The question remains whether it is
worth expending development effort and planning cycles to have a more
consistent definition.  What's the use-case?

			regards, tom lane

In response to

pgsql-bugs by date

Next:From: Tom LaneDate: 2006-11-27 22:39:30
Subject: Re: Unexpected sort order.
Previous:From: Ron MayerDate: 2006-11-27 22:18:59
Subject: Re: Unexpected sort order.

pgsql-general by date

Next:From: Alvaro HerreraDate: 2006-11-27 22:22:33
Subject: Re: fatal error on 8.1 server
Previous:From: Ron MayerDate: 2006-11-27 22:18:59
Subject: Re: Unexpected sort order.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group