Interesting CREATE TABLE AS misbehavior
regression=# select * from int8_tbl union all select * from int8_tbl order by q2;
q1 | q2
------------------+-------------------
4567890123456789 | -4567890123456789
4567890123456789 | -4567890123456789
4567890123456789 | 123
4567890123456789 | 123
123 | 456
123 | 456
4567890123456789 | 4567890123456789
123 | 4567890123456789
4567890123456789 | 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
On Sun, Sep 17, 2006 at 10:10:52PM -0400, Tom Lane wrote:
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?
Isn't the existing behavior that you can't do something (ie, throws an
error)? Doesn't seem like much would break to fix it. Then again, I
can't remember anyone complaining about this, so it's probably not worth
the effort.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
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.
I'd tend to agree that it seems like a bug but I'm a little mystified as
to why an 'order by' would be desired (or allowed) for a 'create table as'
at all. Allowing it seems to imply it has some meaning.
Just my 2c,
Stephen
Stephen Frost <sfrost@snowman.net> writes:
I'd tend to agree that it seems like a bug but I'm a little mystified as
to why an 'order by' would be desired (or allowed) for a 'create table as'
at all. Allowing it seems to imply it has some meaning.
By that logic we should disallow CLUSTER, REINDEX, and a host of other
things that are meaningless according to the SQL data model ;-)
But if you want a plausible use-case, the combination of ORDER BY and
LIMIT certainly seems interesting and useful, even if each one alone
is debatable.
regards, tom lane
On Mon, Sep 18, 2006 at 09:30:43AM -0400, Stephen Frost wrote:
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
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.I'd tend to agree that it seems like a bug but I'm a little mystified as
to why an 'order by' would be desired (or allowed) for a 'create table as'
at all. Allowing it seems to imply it has some meaning.
Because if the ORDER BY is honored, the newly created table will be
clustered in a specific order. That can be very useful in certain
applications.
--
Jim Nasby jimn@enterprisedb.com
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)