NHibernate thinks order by column isn't in select

Question

I'm trying to get page 2 of results of a query, this is the prepared statement SQL query my code builds:

SELECT DISTINCT Contents.*
FROM Contents
INNER JOIN ContentsFilter ON ContentsFilter.ContentId = Contents.ContentId
INNER JOIN Filter ON Filter.Id = ContentsFilter.FilterId
WHERE Contents.[Key] LIKE 'kh_%'
AND Filter.Name IN (:filters)
ORDER BY Contents.Created DESC

Which is then paramterised and such, and SetFetchSize() and SetFirstResult() are called on the constructed IQuery. On page 1, this works fine, but on page 2 I get this exception:

NHibernate.HibernateException: The dialect was unable to perform paging of a statement that requires distinct results, and is ordered by a column that is not included in the result set of the query.
   at NHibernate.Dialect.MsSql2005DialectQueryPager.BuildFromClauseForPagingDistinctQuery(MsSqlSelectParser sqlQuery, SqlStringBuilder result)
   at NHibernate.Dialect.MsSql2005DialectQueryPager.PageByLimitAndOffset(SqlString offset, SqlString limit)
   at NHibernate.Dialect.MsSql2005DialectQueryPager.PageBy(SqlString offset, SqlString limit)
   at NHibernate.Dialect.MsSql2005Dialect.GetLimitString(SqlString queryString, SqlString offset, SqlString limit)
   at NHibernate.Dialect.Dialect.GetLimitString(SqlString queryString, Nullable`1 offset, Nullable`1 limit, Parameter offsetParameter, Parameter limitParameter)
   at NHibernate.Loader.Loader.TryGetLimitString(Dialect dialect, SqlString queryString, Nullable`1 offset, Nullable`1 limit, Parameter offsetParameter, Parameter limitParameter, SqlString& result)
   at NHibernate.Loader.Loader.AddLimitsParametersIfNeeded(SqlString sqlString, ICollection`1 parameterSpecs, QueryParameters queryParameters, ISessionImplementor session)
   at NHibernate.Loader.Loader.CreateSqlCommand(QueryParameters queryParameters, ISessionImplementor session)
   at NHibernate.Loader.Loader.PrepareQueryCommand(QueryParameters queryParameters, Boolean scroll, ISessionImplementor session)
   at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies, IResultTransformer forcedResultTransformer)
   at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies, IResultTransformer forcedResultTransformer)
   at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters, IResultTransformer forcedResultTransformer)
   at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)
   at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters)
   at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes)
   at NHibernate.Loader.Custom.CustomLoader.List(ISessionImplementor session, QueryParameters queryParameters)
   at NHibernate.Impl.SessionImpl.ListCustomQuery(ICustomQuery customQuery, QueryParameters queryParameters, IList results)
   at NHibernate.Impl.AbstractSessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters, IList results)
   at NHibernate.Impl.AbstractSessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters)
   at NHibernate.Impl.SqlQueryImpl.List()
   at MySite.Data.NHibernate.Shell.KnowledgeHubRepository.ExecuteKnowledgeHubQuery(IQuery query) in C:\Work\MySite\src\app\MySite.Data.NHibernate\Shell\KnowledgeHubRepository.cs:line 194
   at MySite.Data.NHibernate.Shell.KnowledgeHubRepository.FindByFilter(String[] filters, Int32 page) in C:\Work\MySite\src\app\MySite.Data.NHibernate\Shell\KnowledgeHubRepository.cs:line 174
   at Castle.Proxies.Invocations.IKnowledgeHubRepository_FindByFilter.InvokeMethodOnTarget()
   at Castle.DynamicProxy.AbstractInvocation.Proceed()
   at MySite.Core.App.Ioc.Interceptors.StopwatchInterceptor.Intercept(IInvocation invocation) in C:\Work\MySite\src\app\MySite.Core\App\Ioc\Interceptors\StopwatchInterceptor.cs:line 11
   at Castle.DynamicProxy.AbstractInvocation.Proceed()
   at Castle.Proxies.IKnowledgeHubRepositoryProxy.FindByFilter(String[] filters, Int32 page)
   at MySite2.Web.Controllers.KnowledgeHubController.Get(Int32 page, String filters) in C:\Work\MySite\src\app\Website\Controllers\KnowledgeHubController.cs:line 119
   at lambda_method(Closure , ControllerBase , Object[] )
   at System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters)
   at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)
   at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
   at System.Web.Mvc.ControllerActionInvoker.<>c__DisplayClass15.<InvokeActionMethodWithFilters>b__12()
   at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation)
   at System.Web.Mvc.ControllerActionInvoker.<>c__DisplayClass15.<>c__DisplayClass17.<InvokeActionMethodWithFilters>b__14()
   at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodWithFilters(ControllerContext controllerContext, IList`1 filters, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
   at MySite.Core.App.Ioc.WindsorActionInvoker.InvokeActionMethodWithFilters(ControllerContext controllerContext, IList`1 filters, ActionDescriptor actionDescriptor, IDictionary`2 parameters) in C:\Work\MySite\src\app\MySite.Core\App\Ioc\WindsorActionInvoker.cs:line 21
   at System.Web.Mvc.ControllerActionInvoker.InvokeAction(ControllerContext controllerContext, String actionName)

I'm very clearly selecting * so I didn't think the exception message could be correct, and sure enough if I change it to SELECT DISTINCT Contents.*, Contents.Created sure enough I get a System.Data.SqlClient.SqlException: The column 'Created' was specified multiple times for 'q_'.

So what does this error message really mean?

I'm using NHibernate 4.0.4.4000 and SQL Server Express 2008 64-bit 10.0.6.

Answer 1

I guess you use ISession.CreateSqlQuery. I was not even knowing NHibernate would try parsing an arbitrary SQL query for injecting paging in it, but it does that indeed.

Unfortunately for you, your case is not covered. The first page works because it does not have to apply an offset, it just has to inject a top statement after the select. That is quite a simple 'parse and alter the query' case, without many checks to perform. See PageByLimitOnly in MsSql2005DialectQueryPager.cs.

The second and next pages blows away because it has to inject a row_number() over (order by yourOrderBy) where condition statement for offsetting with SQL Server 2008/2005, which is quite more complex.
NHibernate has to rewrite the order by and current implementation guards against rewriting an invalid order by depending on a column not present in the distinct. Old SQL Server versions where supporting distinct queries ordered by a column which was not selected, and this case would cause NHibernate paging logic for SQL Server 2005/2008 to fail. (SQL server 2000 was supporting that distinct case, but I think it was dropped in SQL Server 2005, so maybe there is another reason for NHibernate doing that indeed.)
But current implementation of the check does not account for *. See BuildFromClauseForPagingDistinctQuery in MsSql2005DialectQueryPager.cs.

So well, since you are already supplying your own SQL, why not inserting your own paging statement in it?

Otherwise you need to try supporting your case with a pull-request on NHibernate and wait for its merge and release.

Or maybe upgrade to SQL 2012 and set NHibernate dialect to MsSql2012Dialect: it supports offset fetch SQL statements, which are simpler to inject in an arbitrary SQL query.