Re: ORDER BY and UNION

Michael Fork <mfork(at)toledolink(dot)com> writes:
> However, if I execute the same query and drop "a.attnum as number" from
> the select part, it returns the following:
> ...
> 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?

I believe that this query should not be considered valid --- and, in
fact, current sources will return an error if you try to ORDER a UNION
result by something that's not one of the output columns of the UNION.

The issue is that if you are union'ing arbitrary queries together,
how do you decide what the ORDER BY expression means in the context
of each component query? Consider

select a, b from tab1
UNION
select c, d from tab2
ORDER BY z;

Even assuming that there are columns named z in both tables, the ORDER
BY would be exceeding its authority to assume that those columns are
what is meant. Furthermore, since we're doing a UNION here, the result
will be reduced to just the unique output rows, meaning that there might
be more than one possible z value for each output row; so the sort order
wouldn't be well-defined.

It seems to me that for UNION-type queries we need to stick to the
letter of the SQL standard and only allow ORDER BY an output column
name. In my example you'd be allowed to do "ORDER BY a" or equivalently
"ORDER BY 1", but not "ORDER BY z".

Existing releases fail to defend against this situation, and produce a
plan that does who-knows-what. In current sources you'll get an error:

regression=# select q2 from int8_tbl union all select q2 from int8_tbl
regression-# order by int8_tbl.q1;
ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of the result columns

regards, tom lane