Re: [HACKERS] Interesting CREATE TABLE AS misbehavior
* Tom Lane ([EMAIL PROTECTED]) 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 signature.asc Description: Digital signature
Re: [HACKERS] Interesting CREATE TABLE AS misbehavior
Stephen Frost [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Interesting CREATE TABLE AS misbehavior
On Mon, Sep 18, 2006 at 09:30:43AM -0400, Stephen Frost wrote: * Tom Lane ([EMAIL PROTECTED]) 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[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Interesting CREATE TABLE AS misbehavior
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[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
