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?
Best Answer
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:
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.