Ambiguous order by?
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
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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:
Show quoted text
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 bysortable_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