Sometimes, SELECT Really Is Broken

We got a bug report from a customer the other day; a certain query in one of our web services was giving the following error:

A column has been specified more than once in the order by list. Columns in the order by list must be unique.

Seems clear enough, except that

1. There was no duplication in the ORDER BY. Our DBA discovered that the "problem" reference was [Project2].[C5] ASC, which is an alias, not a real DB column name. It certainly appeared only once, but removing it made the error go away. There is documentation which implies that this was an intentional change in SQL Server 2005, but:


2. The same query worked fine on other SQL Server 2005 servers. The query work correctly on a local QA environment. But it failed on the customer's database.


3. We didn't write the query; it was produced by the Entity Framework, and my experience tells me that the SQL Server provider for EF doesn't tend to produce un-compilable SQL.

In order to isolate the problem, I ran the LINQ query in LINQPad. This allowed me to reproduce the problem outside the context of the application (useful, since the test case took a couple of minutes, whereas executing the query in LINQPad directly is almost instant), and also allowed me to switch DB servers easily.

I found a workaround -- I could change the LINQ and produce SQL which would work on both servers. But I didn't like this "fix" because I still didn't understand the problem. I don't want to "fix" one thing but cause another problem for some other customer. So I dug a little deeper.

Googling turned up information about the actual problem this error message is supposed to explain, but that wasn't the case with our query. I did learn that we are not the first group to hit this problem, and that some people "fix" it by setting the database compatibility level to SQL Server 2000. But I don't like this "fix" at all. Changing the database compatibility level would mean that I would also have to change the ProviderManifestToken in the application's EDM ask, causing the Entity Framework to emit SQL Server 2000-compatible SQL. That would make the entire application less efficient. So I kept looking.

This Stack Overflow answer didn't explain everything, but provided an important clue. We checked the server versions on both our local QA environment and the customer system. The customer server was running SQL Server 2005, SP 3. Our local QA environment was running SQL Server 2005, SP 4. Further experimentation showed that this was, in fact, the problem.

It's generally true that "SELECT isn't broken." But when you've ruled out every other possibility, it's sometimes worth checking!