I run into a bug today with the way NHibernate dealt with order clauses. In particular, it can only happen if you are:
- Use parameters in the order clause
- Using SQL Server 2005
- Using a limit clause
If you met all three conditions, you would run into a whole host of problems (in particular, NH-1527 and NH-1528). They are all fixed now, and I am writing this post as the build run. The underlying issue is that SQL Server 2005 syntax for paging is broken, badly.
Let us take the this statement:
SELECT THIS_.ID AS ID0_0_, THIS_.AREA AS AREA0_0_, THIS_.PARENT AS PARENT0_0_, THIS_.PARENTAREA AS PARENTAREA0_0_, THIS_.TYPE AS TYPE0_0_, THIS_.NAME AS NAME0_0_ FROM TREENODE THIS_ WHERE THIS_.NAME LIKE ? AND THIS_.ID > ? ORDER BY (SELECT THIS_0_.TYPE AS Y0_ FROM TREENODE THIS_0_ WHERE THIS_0_.TYPE = ?) ASC
And let us say that we want to get a paged view of the data. How can we do it? Here is the code:
SELECT TOP 1000 ID0_0_, AREA0_0_, PARENT0_0_, PARENTAREA0_0_, TYPE0_0_, NAME0_0_ FROM (SELECT ROW_NUMBER() OVER(ORDER BY __HIBERNATE_SORT_EXPR_0__) AS ROW, QUERY.ID0_0_, QUERY.AREA0_0_, QUERY.PARENT0_0_, QUERY.PARENTAREA0_0_, QUERY.TYPE0_0_, QUERY.NAME0_0_, QUERY.__HIBERNATE_SORT_EXPR_0__ FROM (SELECT THIS_.ID AS ID0_0_, THIS_.AREA AS AREA0_0_, THIS_.PARENT AS PARENT0_0_, THIS_.PARENTAREA AS PARENTAREA0_0_, THIS_.TYPE AS TYPE0_0_, THIS_.NAME AS NAME0_0_, (SELECT THIS_0_.TYPE AS Y0_ FROM TREENODE THIS_0_ WHERE THIS_0_.TYPE = ?) AS __HIBERNATE_SORT_EXPR_0__ FROM TREENODE THIS_ WHERE THIS_.NAME LIKE ? AND THIS_.ID > ?) QUERY) PAGE WHERE PAGE.ROW > 10 ORDER BY __HIBERNATE_SORT_EXPR_0__
Yes, in this case, we could use TOP 1000 as well, but that doesn't work if we want pages data that isn't started at the beginning of the data set.
Now, here is an important fact, the question marks that you see? Those are positional parameters. Do you see the bug now?
SQL Server 2005 (and 2008) paging support is broken. I find it hard to believe that a feature that is just a tad less important than SELECT is so broken. Any other database get it right, for crying out load.
Anyway, by now you noticed that when we processed the statement to add the limit clause, we had re-written the structure of the statement and changed the order of the parameters. Tracking that problem down was a pain, just to give an idea, here is a bit of the change that I had to make:
/// <summary> /// We need to know what the position of the parameter was in a query /// before we rearranged the query. /// This is used only by dialects that rearrange the query, unfortunately, /// the MS SQL 2005 dialect have to re shuffle the query (and ruin positional parameter /// support) because the SQL 2005 and 2008 SQL dialects have a completely broken /// support for paging, which is just a tad less important than SELECT. /// See NH-1528 /// </summary> public int? OriginalPositionInQuery;
I fixed the issue, but it is an annoying problem that keep occurring. Paging in SQL Server 2005/8 is broken!
Oh, and just to clarify some things. The ability to use complex expressions for the order by clause using the projection API is fairly new for NHibernate, it is incredibly powerful and really scares me.