Thread: Bug #691: CREATE TABLE AS ignores explicit column names with UNION

Todd Reed (treed@kojents.com) reports a bug with a severity of 3
The lower the number the more severe it is.

Short Description
CREATE TABLE AS ignores explicit column names with UNION

Long Description
CREATE TABLE AS ignores explicitly specified column names when the query contains a UNION.  This is illustrated in the
examplebelow. 

I am using 7.2.1 on RedHat Linux 7.2.

Sample Code
template1=# create table foo (
template1(#     x       int,
template1(#     y       int,
template1(#     z       int
template1(# );
CREATE
template1=# create table bar (
template1(#     a       int,
template1(#     b       int,
template1(#     c       int
template1(# );
CREATE
template1=# create table snafu (s, u, t) as
template1-# select x, y, z from foo union select a, b, c from bar;
SELECT
template1=# create table spam (s, u, t) as
template1-# select x, y, z from foo;
SELECT
template1=# select * from snafu;
 x | y | z
---+---+---
(0 rows)


template1=# select * from spam;
 s | u | t
---+---+---
(0 rows)

No file was uploaded with this report

pgsql-bugs@postgresql.org writes:
> CREATE TABLE AS ignores explicitly specified column names when the query contains a UNION.  This is illustrated in
theexample below. 

Good catch!  If you need a fix immediately, I think the attached patch
will do the trick.  (Line numbers are for current sources, but it should
patch cleanly into 7.2.*)  I have not tested it extensively though, so
there might be side-effects...

            regards, tom lane

*** src/backend/parser/analyze.c.orig    Tue May 28 18:15:42 2002
--- src/backend/parser/analyze.c    Wed Jun 12 21:49:56 2002
***************
*** 2212,2219 ****
--- 2212,2227 ----
          qry->isBinary = FALSE;
      }

+     /*
+      * Any column names from CREATE TABLE AS need to be attached to both the
+      * top level and the leftmost subquery.  We do not do this earlier
+      * because we do *not* want the targetnames list to be affected.
+      */
      if (intoColNames)
+     {
          applyColumnNames(qry->targetList, intoColNames);
+         applyColumnNames(leftmostQuery->targetList, intoColNames);
+     }

      /*
       * As a first step towards supporting sort clauses that are