Обсуждение: version issue?

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)

I'm figuring it is in the ORDER BY, but I'm not sure.  Can anyone help me
out?

-Dan

* Dan Wilson <phpPgAdmin@acucore.com> [010225 22:47] wrote:
> 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)
>
> I'm figuring it is in the ORDER BY, but I'm not sure.  Can anyone help me
> out?

(just a guess) try adding the table name, like: 'u.last_name'.

--
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]

Ok... here's what I tried. I was mistaken in my beta version.

sib=# select version();
                                version
------------------------------------------------------------------------
 PostgreSQL 7.1beta3 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66
(1 row)

sib=# 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 mission_id IN ('36') 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);

ERROR:  Attribute 'last_name' not found

sib=# 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 mission_id IN ('36') 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(u.last_name);

ERROR:  Relation 'u' does not exist

sib=# 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 mission_id IN ('36') 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(user_info.last_name);

ERROR:  ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of the
result columns

Is this a bug in beta3?  One of those combinations should work!

-Dan

----- Original Message -----
From: "Alfred Perlstein" <bright@wintelcom.net>


> * Dan Wilson <phpPgAdmin@acucore.com> [010225 22:47] wrote:
> > 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)
> >
> > I'm figuring it is in the ORDER BY, but I'm not sure.  Can anyone help
me
> > out?
>
> (just a guess) try adding the table name, like: 'u.last_name'.
>
> --
> -Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
>

"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

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>

: "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

OK... I tried adding UPPER(last_name) to the result column lists of both
sides of the union and it still gives me the same error.

How can I fix this?

-Dan

"Dan Wilson" <phpPgAdmin@acucore.com> writes:
> : 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.

> OK... I tried adding UPPER(last_name) to the result column lists of both
> sides of the union and it still gives me the same error.

You have to use the SQL-standard syntax for ORDER BY, ie column name or
number, no shortcuts:

    SELECT ..., UPPER(last_name) AS upper_last_name
    UNION
    SELECT ..., UPPER(last_name) AS upper_last_name
    ORDER BY upper_last_name;

or if you prefer, ORDER BY n where n is the ordinal number of the
column.

            regards, tom lane

Excellent! That worked! Thank you once again Tom!  I was under the
impression that you couldn't use an alias in the ORDER BY.  Obviously, I was
mistaken.

Thanks,
-Dan

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>

: "Dan Wilson" <phpPgAdmin@acucore.com> writes:
: > : 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.
:
: > OK... I tried adding UPPER(last_name) to the result column lists of both
: > sides of the union and it still gives me the same error.
:
: You have to use the SQL-standard syntax for ORDER BY, ie column name or
: number, no shortcuts:
:
: SELECT ..., UPPER(last_name) AS upper_last_name
: UNION
: SELECT ..., UPPER(last_name) AS upper_last_name
: ORDER BY upper_last_name;
:
: or if you prefer, ORDER BY n where n is the ordinal number of the
: column.
:
: regards, tom lane