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

Tom,

thanks for your input. Now I have something to bash my
programmers over the head with ;-)

No, just kidding... But as you say, it is plain bad SQL, I
realize this fully now. Oddly enough, I have a SQL book that
has one or two examples with order by a.foobar. Oh well...
Thanks for the prompt answer!

/Palle

Tom Lane wrote:
>
> girgen@partitur.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