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?