order by using functions under unions

From: "Eric Pare" <paree(at)LEXUM(dot)UMontreal(dot)CA>
To: <pgsql-bugs(at)postgresql(dot)org>
Subject: order by using functions under unions
Date: 2001-05-10 19:25:03
Message-ID: 003601c0d986$e97c5ae0$3488cc84@lexum.umontreal.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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') looks 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
---------
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
---------
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 name, 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 reported 100+ times...!

Eric Pare
paree(at)lexum(dot)umontreal(dot)ca

Browse pgsql-bugs by date

  From Date Subject
Next Message pgsql-bugs 2001-05-10 19:29:59 order by using functions under unions
Previous Message Peter Eisentraut 2001-05-10 18:32:54 Re: Paths for C functions (was Re: Re: backend dies on 7.1.1 loading large datamodel.)