"Dan Wilson" <phpPgAdmin@acucore.com> writes:
> I have the following query running on two different servers. It works on
> 7.0.3 and gives the following error on 7.1beta4.
> ERROR: Attribute 'last_name' not found
> Your SQL statement:
> SELECT first_name, last_name, middle_name, u.uid, end_year
> FROM user_info u, auth a
> WHERE u.uid = pri_key AND a.auth_table = 'user_info' AND live = 't'
> AND site_id IN ('214') AND u.end_year > date_part('year', date 'today') - 2
> UNION
> SELECT first_name, last_name, middle_name, u.uid, end_year
> FROM user_info u, current c
> WHERE u.uid = c.uid
> ORDER BY UPPER(last_name)
7.0.3 does not really work in this example (didn't you ever eyeball the
resulting sort order closely??). 7.1 does not support the example at
all. The error message from beta4 is not very good, but beta5 says the
right thing:
ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of the result columns
Possibly some future release will actually do the right thing with ORDER
BY of an expression on the output columns, but right now it has to be an
output column, period.
regards, tom lane