regression=# select * from int8_tbl union all select * from int8_tbl order by q2; q1 | q2
------------------+-------------------4567890123456789 | -45678901234567894567890123456789 |
-45678901234567894567890123456789| 1234567890123456789 | 123 123 |
456 123 | 4564567890123456789 | 4567890123456789 123 |
45678901234567894567890123456789| 4567890123456789 123 | 4567890123456789
(10 rows)
regression=# create table fooey(z1,z2) as select * from int8_tbl union all select * from int8_tbl order by q2;
ERROR: column "q2" does not exist
LINE 1: ... from int8_tbl union all select * from int8_tbl order by q2;
^
The reason for this behavior is that analyze.c attaches the CREATE TABLE
AS aliases to the targetlist (via applyColumnNames) before it processes
the ORDER BY clause. (So, "order by z2" works instead.) This seems
like a bug: one would expect that the aliases do not change the
semantics of the SELECT part of the command.
Interestingly, 7.2 seems to get it right, the misbehavior appears in
7.3 and later.
I'm inclined to fix this in HEAD but not back-patch it, on the grounds
that there might be apps out there expecting the existing behavior,
and it's not a big enough deal to change behavior in a minor release.
Thoughts?
regards, tom lane
