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.