ORDER BY and UNION - Mailing list pgsql-hackers

Is the following expected behavior for a UNION query with ORDER BY:

executing this query:

+++++++++++++++++++++++++++++++++++++++++++

SELECT   a.attnum as number,         a.attname as attribute,        CASE WHEN t.typname = 'varchar' THEN
t.typname|| '(' || a.atttypmod - 4 || ')'         ELSE                t.typname         END as type,        CASE WHEN
a.attnotnull= 't' THEN                'not null '::text ELSE ''::text         END || 'default ' ||         CASE WHEN
a.atthasdef= 't' THEN                substring(d.adsrc for 128)::text         ELSE ''::text END as modifier
 
FROM     pg_class c,         pg_attribute a,         pg_type t,        pg_attrdef d
WHERE    c.relname = 'tblplayer' AND         a.attnum > 0 AND         a.attrelid = c.oid AND         a.atttypid = t.oid
AND       c.oid = d.adrelid AND        d.adnum = a.attnum
 
UNION ALL 
SELECT   a.attnum as number,         a.attname as attribute,         CASE WHEN t.typname = 'varchar' THEN
t.typname || '(' || a.atttypmod - 4 || ')'         ELSE                t.typname         END as type,        CASE WHEN
a.attnotnull= 't' THEN                'not null '::text         ELSE                ''::text         END as modifier
 
FROM     pg_class c,         pg_attribute a,         pg_type t
WHERE    c.relname = 'tblplayer' AND         a.attnum > 0 AND         a.attrelid = c.oid AND         a.atttypid = t.oid
AND       a.attname NOT IN (SELECT a.attname                           FROM pg_class c,
pg_attributea,                                pg_attrdef d                          WHERE c.relname = 'tblplayer' AND
                             a.attnum > 0 AND                                 a.attrelid = c.oid AND
            a.atttypid = t.oid AND                                 c.oid = d.adrelid AND
d.adnum = a.attnum)
 
ORDER BY a.attnum;

+++++++++++++++++++++++++++++++++++++++

yields
number |   attribute   |    type     |                        modifier                         
--------+---------------+-------------+--------------------------------     1 | play_id       | int4        | not null
defaultnextval('...     2 | play_name     | varchar(30) | not null      3 | play_username | varchar(16) | not null
4| play_password | varchar(16) | not null      5 | play_online   | bool        | default 'f'
 


However, if I execute the same query and drop "a.attnum as number" from
the select part, it returns the following:
  attribute   |    type     |                        modifier                         
---------------+-------------+--------------------------------play_id       | int4        | not null default
nextval('...play_online  | bool        | default 'f'play_name     | varchar(30) | not null play_username | varchar(16)
|not null play_password | varchar(16) | not null 
 

which is incorrect accoring to the initial query.  It appears to be
ordering the individual selects and then appending the second query to
the first -- is this correct?

Thanks

Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio