Re: select a.name ... union select a.name ... order by a.name fails in 7.1

girgen(at)partitur(dot)se writes:
> I'm not certain this is correct SQL, and I know the rewrite is
> easy, but here is a difference I found between 7.0.3 and 7.1:

> select a.name from users a
> union
> select a.name from oldusers a
> order by a.userid;

> ERROR: Relation 'a' does not exist

It's not correct SQL, and Postgres doesn't support it.

> This works fine in postgres 7.0.x

No it doesn't. Consider the following example (done with 7.0.*):

play=> create table foo (f1 int, f2 int);
CREATE
play=> insert into foo values (1,10);
INSERT 1021258 1
play=> insert into foo values (2,9);
INSERT 1021259 1
play=> insert into foo values (3,8);
INSERT 1021260 1
play=> select a.f1 from foo a union select a.f1 from foo a;
f1
----
1
2
3
(3 rows)

-- so far so good, but:

play=> select a.f1 from foo a union select a.f1 from foo a
play-> order by a.f2;
f1
----
3
2
1
2
3
(5 rows)

-- wow, ORDER BY produces a different resultset!

While this is obviously broken, the more fundamental point is that
the only sensible ordering of a UNION result is on one of the result
columns. Otherwise the ordering isn't well-defined: if the UNION merges
equal values of "name" from the two SELECTs, which SELECT's "userid"
will get used for the sort? So the SQL standard mandates ORDER BY
only on output column names or numbers, and we enforce that in 7.1.
Prior versions failed to notice that there was a problem, and would
do something fairly random instead :-(

> the simple rewrite is of course 'order by userid', but it is
> tedious to find all places in our system where this happens. It
> seems some programmers have used the above syntax a lot :(

They haven't thought about whether the query is reasonable.
I'd also say that they never looked closely to see if the output
they were getting was reasonable ...

regards, tom lane