Order by bug on SQL Server 2005 vs SQL Server 2012

Question

I have a query generated by entity framework that returns wrong results on a SQL Server 2005 database (Microsoft SQL Server 2005 - 9.00.5000.00 (X64)) but good on SQL Server 2012 (Microsoft SQL Server 2012 - 11.0.2100.60).

Here is the query :

SELECT 
[Project2].[tableAid] AS [id],     
[Project2].[rank1] AS [rank]   
FROM ( SELECT 
    [Extent1].[id] AS [id], 
    [Extent2].[rank] AS [rank],        
    [Extent4].[rank] AS [rank1]      
    FROM    [dbo].[tableA] AS [Extent1]
    LEFT OUTER JOIN [dbo].[tableB] AS [Extent2] ON ([Extent1].[tableAid] = [Extent2].[tableAid]) AND (896 = [Extent2].[tableBid])
    INNER JOIN [dbo].[tableC] AS [Extent4] ON [Extent1].[tableCid] = [Extent4].[tableCid]        
)  AS [Project2]
ORDER BY [Project2].[rank] DESC

I've simplified it but the model is :

TableA
int tableAid
int tableCid

tableB
int tableBid
int tableAid
int rank

tableC
int tableCid
int rank

There's a many to many relationship between A and B and a 1 to 1 between A and C.

On SQL Server 2005, this query is sorted by the rank column of the results, not by Project2.rank. If I replace the [Project2].[rank1] AS [rank] in the SELECT by [Project2].[rank1] AS [whatever], results are correctly sorted. But since these aliases are generated by EF, I can't easily change them. On SQL Server 2012, the query works correctly as is.

Does anyone know this bug? Is there a patch or some settings for SQL Server 2005 to avoid this problem?

Answer 1

If you are running on compatibility level of 80 (SQL-Server 2000), then this is the expected behaviour. It was corrected in version 2005.

You can check the Compatibility levels page at MSDN where the differences are listed. In the section "Differences Between Lower Compatibility Levels and Level 90", one of the many items is:

When binding the column references in the ORDER BY list to the columns defined in the SELECT list, column ambiguities are ignored and column prefixes are sometimes ignored. This can cause the result set to return in an unexpected order.

For example, an ORDER BY clause with a single two-part column (.) that is used as a reference to a column in a SELECT list is accepted, but the table alias is ignored. Consider the following query.

SELECT c1 = -c1 FROM t_table AS x ORDER BY x.c1

When executed, the column prefix is ignored in the ORDER BY. The sort operation does not occur on the specified source column (x.c1) as expected; instead it occurs on the derived c1 column that is defined in the query. The execution plan for this query shows that the values for the derived column are computed first and then the computed values are sorted.

which is exactly what you describe.

The easiest workaround is to not use the same alias for one column in the subquery and for another in the main query.