Thread

1. order by using functions under unions

   PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2001-05-10T19:29:59Z

   Eric Pare (paree@lexum.umontreal.ca) reports a bug with a severity of 3
   The lower the number the more severe it is.
   
   Short Description
   order by using functions under unions
   
   Long Description
   When using union over queries, if the order by clause occurs using a function, the output will not be correctly ordered.  You need to query the function and then use the alias to order over it.
   
   Sample Code
   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@lexum.umontreal.ca
   
   No file was uploaded with this report
   
   
   

2. Re: order by using functions under unions

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-05-10T21:50:50Z

   pgsql-bugs@postgresql.org writes:
   > 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;
   
   This isn't supported.  7.1 knows that it can't do it:
   
   regression=# select name from temp where name like 'e%'
   regression-# union
   regression-# select name from temp where name not like 'e%'
   regression-# order by substr(name,1,4) asc;
   ERROR:  ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of the result columns
   
   It'd be nice to make it happen for the case you illustrate (ORDER BY on
   an expression using only result columns) but that's not done yet.
   
   In the meantime you can work around it (again, in 7.1) by using an
   explicit subselect:
   
   regression=# select name from (
   regression(# select name from temp where name like 'e%'
   regression(# union
   regression(# select name from temp where name not like 'e%'
   regression(# ) ss
   regression=# order by substr(name,1,4) asc;
     name
   ---------
    chantal
    daniel
    eric
    ernst
   (4 rows)
   
   			regards, tom lane