Working around SQL Server bugs
time to read 3 min | 455 words

Okay, I mentioned that I am working on fairly complex piece of query code, something that is completely off the charts, even for me. As such, I have completely test driven that. How do I Unit Test a complex query? By using in memory database (in this case, SQLite). NHibernate makes it very easy to switch databases, and I never gave it much thought after making it work for the first time.

Until today, where it was time to do an additional tweak to my query. It worked perfectly on the tests, running against SQLite, but failed when running the application against SQL Server. Here is the query that demonstrate the problem:

select address from Address address, City city left join fetch address.Street
             where city.Name = address.City

The generated query looked like:

select [fields list] from Addresses addresses0_, Cities cities1_ left outer join Streets street2_ on addresses0_.Street = street2_.Id  
            where...

This fails which unknown "addresses0_.Street" error, I run into the problem before, and I looks like a bug in SQL Server, or a nasty (appernatly undocumented) surprise.

This was my critical query, I had to run it. In desperation, I opened NHibernate source, and started debugging into it, in order to see how the query is being composed. Haven't looked very closlely at this area in a while, and things have changed somewhat, but I had that sinking feeling that it was one of those really big changes that I am really not ready to make close to the time that I want to go home.

Then I did something that I was sure would fail, I changed the query to:

select address from Address address left join fetch address.Street , City city
             where city.Name = address.City

It worked, produced SQL that would make SQL Server happy, SQLite was able to process this SQL without any issues either. Problem solved, and will hopefully remain in memory for the next time that I need it.

Oh, and if you didn't notice, today was a jumpy day. I am going to do some deep end stuff to relax.

0 comments

Related posts that you may find interesting:

14 Oct 2006 Unit testing with NHibernate / Active Record
C# Databases IoC NHibernate O/R Mappers SQL Test Driven Development XP +5
09 Mar 2006 More about Object Relational Mappers vs. Custom DAL
Databases O/R Mappers
07 Oct 2006 Bringing Active Record and NHibernate Query Generator Together
Castle Databases NHibernate O/R Mappers +1