Skip site navigation (1) Skip section navigation (2)

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Bruno Wolff III <bruno(at)wolff(dot)to>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: possible 7.1beta3 bug with union and order by a function
Date: 2001-01-11 19:14:25
Message-ID: 12223.979240465@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
Bruno Wolff III <bruno(at)wolff(dot)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

In response to

pgsql-bugs by date

Next:From: Bruce MomjianDate: 2001-01-11 21:24:00
Subject: Re: Re: Interval bug
Previous:From: pgsql-bugsDate: 2001-01-11 16:39:10
Subject: JDBC PreparedStatement.setMaxRows() affects other objects intantiated from this class and it's parent class

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group