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.
I don't know what standards say about this, I imagine they don't have anything
relevant because sequences are nonstandard. But I'm pretty sure I recall doing
queries like this all the time in Oracle and not seeing behaviour like this.
Note that this makes the resulting data different if the index exists vs if it
doesn't. I see the same behaviour with INSERT (...) (SELECT...) so it's not
just CREATE TABLE AS doing it.
As I said I'm not sure this is wrong, just wanted to mention it in case it's
not intentional.
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)
slo=> create index idx_w on w1(w);
CREATE INDEX
slo=> set enable_seqscan = off;
SET
slo=> create table w3 as (select nextval('w'),w from w1 order by w desc);
SELECT
slo=> select * from w3;
nextval | w
---------+---
4 | 3
5 | 2
6 | 1
(3 rows)
--
greg