Thread

1. possible 7.1beta3 bug with union and order by a function

   Bruno Wolff III <bruno@wolff.to> — 2001-01-10T07:34:23Z

   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 as title from wbc where gameid is null order by code, lower(title);
   ERROR:  Attribute 'title' not found
   
   

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

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-01-11T19:14:25Z

   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 as title 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