Re: possible 7.1beta3 bug with union and order by a function

Bruno Wolff III <bruno@wolff.to> writes:
> The query below used to work with 7.0.3. If I change 'lower(title)' to
> 'title' in the order by clause, then the query is accepted.

> area=> select code, wbc.gameid, title from games, wbc where wbc.gameid = games.gameid union select code, null, null
astitle from wbc where gameid is null order by code, lower(title); 
> ERROR:  Attribute 'title' not found

It may have *appeared* to work in 7.0.3, but the only reason it avoided
failure was that you were selecting null for title in the second part of
the union, and thus the fact that it was computing garbage for the value
of lower(title) in that part was masked.  In fact queries of this kind
have been broken for a long time.

7.1 only allows the result of a union to be sorted by output-column
names, not by expressions that do not appear in the output.  If you
think about it, anything else is not well-defined because of UNION's
elimination of duplicates: if we do
    SELECT a FROM ... UNION SELECT b FROM ... ORDER BY c;
then there isn't necessarily a unique value of 'c' associated with
any particular output row --- rows with different 'c' values might
have gotten merged because they had the same 'a'/'b' values.
For that matter, if the FROM clauses are different then 'c' may not
even be meaningful within both SELECTs.  Pre-7.1 PG fails to cope with
any of this, however.

The current error message does leave something to be desired, however.
I'll see if I can improve it.

BTW, you could probably do this query much more simply now by using
an outer join instead of a union ...

            regards, tom lane