order by clause incorrectly produces "ambiguous column name" - by xor88

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 644134 Comments
Status Closed Workarounds
Type Bug Repros 1
Opened 2/15/2011 3:35:00 AM
Access Restriction Public


Execute this:

SELECT object_id, *
FROM sys.objects
ORDER BY object_id


Msg 209, Level 16, State 1, Line 3
Ambiguous column name 'object_id'.

I think the column name is not ambiguous (only one table present).
Sign in to post a comment.
Posted by LeoTohill on 8/14/2013 at 11:58 AM
Upvoted, because this thread answered the question for me. But as a followup (somewhat rhetorical) question, suppose you had the query

select upper(col1) as col1, col1 from table1 order by col1

This generates the ambiguity error. Now suppose I wanted to order by the upper(col1) result column. The only way I know to do this is by specifying ORDER BY 1 . If true, then I could argue that there is no ambiguity, because "ORDER BY col1" can only mean table1.col1. There is no way it could mean upper(col1) because you can't refer to that column by name.

Just sayin'.
Posted by 1hb2 on 7/28/2011 at 4:26 PM
There is one thing to mention: if you change the compatibilty mode of your DB in MS SQL you will experience different behavious on this. In SQL Server 2000 (80) your query will not throw any exception but it does in MS SQL 2005 and above.
Posted by xor88 on 2/16/2011 at 1:43 AM
And even in the general case the two columns will always return the same value. That is because you cannot refer to a modified version of that column by name. My example shows this.
Posted by xor88 on 2/15/2011 at 10:37 PM
That is strange because I thought the name "object_id" in the order by clause refers to sys.objects.object_id and not to my own projection. Because you cannot do this:

SELECT object_id + 1 as x
FROM sys.objects
order by x

Therefore you should not be able to reference your own columns! But the current implementation of the order by clause _can_ reference columns defined in the query which seems to be incorrect.

Posted by Microsoft on 2/15/2011 at 5:29 PM
Hi xor88,

As your SQL sample shows, SQL Server allows two columns with the same name to be returned in a query result. Note that in a general case, the columns in question may return different values (even though this is not the case in your query). That is the reason why ORDER BY clause requires an unambiguous column reference. In your sample, object_id reference is ambiguous because it's not clear which object_id column you reference, the explicit one or the one that comes as part of the "star". This behavior is by design.

Eugene Zabokritski, SQL Engine
Posted by Dave Dustin on 2/15/2011 at 4:59 PM
If you try

SELECT object_id, object_id
FROM sys.objects
ORDER BY object_id

you will get the same error. The order by clause uses the column names as return by the query.

If you were to rewrite as

SELECT object_id, object_id AS a
FROM sys.objects

it works just fine.

Posted by Sankar Reddy on 2/15/2011 at 2:26 PM

Have you tried below?
FROM sys.objects
ORDER BY object_id

Note that in your select object_id is listed twice. Once explicitly and another from the *. Downvoted my friend.