Thread: Interesting CREATE TABLE AS misbehavior
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
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)