Ambiguous order by?

From: Cody Cutrer <cody(at)instructure(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Ambiguous order by?
Date: 2013-05-22 16:41:12
Message-ID: CA+=qeWu5+FUqkYz_Pi9a_LgVusLsep3mv54CGxZpirZ4g9bz-g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Neeraj Rai 2013-05-22 16:52:10 Re: - pgaql binary data date time fornats
Previous Message RDNikeAir 2013-05-22 16:40:27 Re: VACUUM FULL freezes