Re: not exactly a bug report, but surprising behaviour

On 4 Feb 2003, Gregory Stark wrote:

> It seems the sort operation is done very late in the process, after functions
> in the select column list have already been called and the results stored.
> This makes using sequences to generate a sequential list of numbers have a
> surprising behaviour.

Select list entries are done before order by since you can order by the
output of a select list entry.

> slo=> create sequence w;
> CREATE SEQUENCE
> slo=> create table w1 (w integer);
> CREATE TABLE
> slo=> insert into w1 values (1);
> INSERT 229135376 1
> slo=> insert into w1 values (2);
> INSERT 229135377 1
> slo=> insert into w1 values (3);
> INSERT 229135378 1
> slo=> create table w2 as (select nextval('w'),w from w1 order by w desc);
> SELECT
> slo=> select * from w2;
>  nextval | w
> ---------+---
>        3 | 3
>        2 | 2
>        1 | 1
> (3 rows)

I think the query that would give you what you want in this case is
something like the following to force the order before doing the nextvals:

create table w2 as select nextval('w'), w from (select w from w1 order by
w desc) w1;

However, I'm not sure that you can count on that working in all cases
either.