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
