Friday, December 18, 2009

funny thing happened while trying to order

Someone in the QA group encountered an issue with a process they were testing. As they dug into the error, they found that a single stored procedure was erroring. The reason they are testing is on a new instance of our core code and databases, running against SQL 2005. This is exciting to us, as we have pieces still running against SQL 2000. At first, the question is how this piece of code made it into the system, not working. Investigation was done by some members, and questions were raised. After a small amount of detective work, we realized that the code in SQL2005 was identical to SQL2000. But it didn't work in 2005. Armed with this realization, we were able to quickly see the query and the problem. It turns out that the original developers had aliased 1 of the fields. The select statement was returning a handful of fields, 2 of which were ID fields, but from different tables. To differentiate these, the second ID field had been aliased. It was no longer called ID, but was FilterConfigID. This is not the issue, but the start of the issue. Everywhere else in the statement, the table was referenced by name, not by alias. So Table.Field was the naming standard for this select statement. Once the developers got to the Order By statement, they simply followed suit, and called the field they were ordering by Table.Alias, using the single aliased field from the select statement. This worked in SQL2000. But ceased working in SQL20005. The query processor was parsing this, realizing that the fieldname (aliased from ID to ConfigFilterID) didnt exist in the table, and returned an error.
This was a fun little exercise in naming conventions, old code, differences in versions and so on. It was a welcome distraction from the current issue i was dealing with, and i believe all DB folks that were involved with is, on multiple fronts, had a fun few minutes digging into this, realizing the issue, and coming up with a solution.

1 comment:

Jack said...

Interesting. I don't think I've experienced that and I usually use the original column name with the table prefix.