Postgres Pro
Downloads
Home   >   mailing lists

Thread: Ambiguous order by?

Ambiguous order by?

From
Cody Cutrer
Date:
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

Re: Ambiguous order by?

From
Tom Lane
Date:
Cody Cutrer <cody@instructure.com> writes:
> 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...

I think it's unhappy because "sortable_name" could refer to either of
the output columns (under the old SQL92 convention that an ORDER BY item
is an output column name).  Probably the easiest way to dodge that is to
qualify the name, ie ORDER BY test1.sortable_name.  A different line of
attack is to use AS to relabel whichever output column you don't want to
match.

            regards, tom lane

Re: Ambiguous order by?

From
Cody Cutrer
Date:
Okay, so why does wrapping the order by in a function fix it? (or not doing a join, or doing an implicit join)

Cody Cutrer


On Wed, May 22, 2013 at 11:36 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Cody Cutrer <cody@instructure.com> writes:
> 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...

I think it's unhappy because "sortable_name" could refer to either of
the output columns (under the old SQL92 convention that an ORDER BY item
is an output column name).  Probably the easiest way to dodge that is to
qualify the name, ie ORDER BY test1.sortable_name.  A different line of
attack is to use AS to relabel whichever output column you don't want to
match.

                        regards, tom lane