Ambiguous order by?

[Cody Cutrer <cody@xxxxxxxxxxxxxxx>]

I'm not sure if this is a bug, or something I'm not understanding. When I have a column referenced in the select both fully qualified and not fully qualified, an explicit inner join, and that column in the order by (unqualified, and not in a function call), it complains that is ambiguous:

create table test1 (id integer, sortable_name varchar);
create table test2 (id integer, test1_id integer);
select test1.sortable_name, sortable_name from test1 inner join test2 on test1.id=test1_id order by sortable_name;
 
 
ERROR:  ORDER BY "sortable_name" is ambiguous
LINE 1: ...t1 inner join test2 on test1.id=test1_id order by sortable_n...
 
All of these work:
 

select test1.sortable_name, sortable_name from test1 order by sortable_name /* no join */
select test1.sortable_name, sortable_name from test1, test2 where test1.id=test1_id order by sortable_name; /* implicit join */
select test1.sortable_name from test1 inner join test2 on test1.id=test1_id order by sortable_name /* only one sortable_name in select */
select test1.sortable_name, sortable_name from test1 inner join test2 on test1.id=test1_id order by test1.sortable_name /* qualify sortable_name in order, but not select */
select test1.sortable_name, test1.sortable_name from test1 inner join test2 on test1.id=test1_id order by sortable_name /* qualify sortable_name both selects */
select sortable_name, sortable_name from test1 inner join test2 on test1.id=test1_id order by sortable_name /* unqualified everywhere */
select test1.sortable_name, sortable_name from test1 inner join test2 on test1.id=test1_id order by substring(sortable_name,1,2); /* use a function in the order */



I've tried this on 9.1.4, 9.1.9, and 9.2.3.

The actual application usage looks more like SELECT users.*, sortable_name FROM users <single join and multiple where clauses> ORDER BY sortable_name. The application code always appends sortable_name to the select list because, depending on available features, sortable_name might be a function call and in a GROUP BY.

Thanks for any insight,


Cody Cutrer