order by using functions under unions

Here is a short example where this touchy bug occurs :

create database mydb;
\c mydb
create table temp ( name varchar(12));
insert into temp values ('eric');
insert into temp values ('daniel');
insert into temp values ('ernst');
insert into temp values ('chantal');

having the four names above, selecting the 2 names starting with an 'e'
and then making the union with the others (the first letter isn't an 'e') l=
ooks like this :

select name from temp where name like 'e%'
union
select name from temp where name not like 'e%'
order by substr(name,1,4) asc;

  name=20=20=20
---------
 eric
 ernst
 chantal
 daniel
(4 rows)

The order by didn't work correctly -- and should be chantal, daniel, eric, =
ernst

Trying somewhat of an oposite :
select name from temp where name not like 'e%'
union
select name from temp where name like 'e%'
order by substr(name,1,4) desc;

  name=20=20=20
---------
 daniel
 chantal
 eric
 ernst
(4 rows)

the problem seems to occur while trying to order over a function, specially=
 because if you remove the substr function and try to "order by" over the n=
ame, the output is correctly ordered.

an easy way to solve this problem is to select what you want to order over =
and then order on the alias (that way you do not order over a function...I =
guess)
i.e. :
select name, substr(name,1,4) from temp where name not like 'e%'
union
select name, substr(name,1,4) from temp whare name like 'e%'
order by substr desc;

hope the example is clear enough and that the bug hasn't already been repor=
ted 100+ times...!

Eric Pare
paree@lexum.umontreal.ca