Order by, expressions & column aliases issue

[Jeff Trout <threshar@xxxxxxxxxxxxx>]

This isn't terribly clear in the documentation.

But it seems, in 8.0.3 and 8.1.1 that you cannot use a column alias  
in an order by if you are using an expression (ie order by  
my_column_alias <> 0 asc). If you use a plain asc/desc order by then  
it is fine (ie order by my_column_alias asc).

Example:
create table tab1 ( x int );

insert into tab1(x) values (0);
insert into tab1(x) values (0);
insert into tab1(x) values (0);
insert into tab1(x) values (1);
insert into tab1(x) values (2);
insert into tab1(x) values (3);
insert into tab1(x) values (4);
insert into tab1(x) values (5);

-- order by column alias, single table. good.
select x as taco from tab1 order by taco desc;

-- order by x descending, with all zeros at end, using column name
select x as taco from tab1 order by x <> 0 desc, x desc;

-- same as above, but use alias
select x as taco from tab1 order by taco <> 0 desc, taco desc;

-- cleanup
drop table tab1;

Output:
CREATE TABLE
INSERT 423464319 1
INSERT 423464320 1
INSERT 423464321 1
INSERT 423464322 1
INSERT 423464323 1
INSERT 423464324 1
INSERT 423464325 1
INSERT 423464326 1
taco
------
    5
    4
    3
    2
    1
    0
    0
    0
(8 rows)

taco
------
    5
    4
    3
    2
    1
    0
    0
    0
(8 rows)

ERROR:  column "taco" does not exist
DROP TABLE

thanks
--
Jeff Trout <jeff@xxxxxxxxxxxxx>
http://www.jefftrout.com/
http://www.stuarthamm.net/