[nhibernate-issues] [NHJIRA] Created: (NH-2360) Projection Criteria Query ordered by SubQuery fails with paging in SqlServer 2005 "Must declare the scalar variable "@p1"."

[Josh R. (JIRA) <nh...@gm...>]

Projection Criteria Query ordered by SubQuery fails with paging in SqlServer 2005 "Must declare the scalar variable "@p1"."
---------------------------------------------------------------------------------------------------------------------------

                 Key: NH-2360
                 URL: http://216.121.112.228/browse/NH-2360
             Project:  NHibernate
          Issue Type: Bug
          Components: Core
    Affects Versions: 2.1.2.GA
            Reporter: Josh Robb
            Priority: Major


Using Ayende's Blog sample from http://github.com/ayende/Advanced.NHibernate.git (need to make sure that the dialect is MsSql2005Dialect). 

Executing the following query: 

            var commentCount = DetachedCriteria.For<Comment>("c")
                .Add(Restrictions.Gt("c.id", 0))
                .Add(Restrictions.EqProperty("c.Post.id", "p.id"))
                .SetProjection(Projections.RowCount());

            var criteria = DetachedCriteria.For<Post>("p")
                .SetProjection(Projections.ProjectionList()
                    .Add(Projections.Property("Id"), "Id")
                    .Add(Projections.Property("Title"), "Title")
                    .Add(Projections.SubQuery(commentCount), "CommentCount"))
                .SetResultTransformer(Transformers.AliasToBean<PostsWithCount>())
                .AddOrder(new Order("CommentCount", false))
                .SetMaxResults(10)
                .SetFirstResult(10);

Results in:

NHibernate.ADOException: could not execute query
[ SELECT TOP 10 y0_, y1_, y2_ FROM (SELECT this_.Id as y0_, this_.Title as y1_,(SELECT count(*) as y0_ FROM Comments this_0_ WHERE this_0_.Id > @p0 and this_0_.PostId = this_.Id) as y2_, ROW_NUMBER() OVER(ORDER BY (SELECT count(*) as y0_ FROM Comments this_0_ WHERE this_0_.Id > @p1 and this_0_.PostId = this_.Id) DESC) as __hibernate_sort_row FROM Posts this_) as query WHERE query.__hibernate_sort_row > 10 ORDER BY query.__hibernate_sort_row ]
Positional parameters:  #0>0
[SQL: SELECT TOP 10 y0_, y1_, y2_ FROM (SELECT this_.Id as y0_, this_.Title as y1_, (SELECT count(*) as y0_ FROM Comments this_0_ WHERE this_0_.Id > @p0 and this_0_.PostId = this_.Id) as y2_, ROW_NUMBER() OVER(ORDER BY (SELECT count(*) as y0_ FROM Comments this_0_ WHERE this_0_.Id > @p1 and this_0_.PostId = this_.Id) DESC) as __hibernate_sort_row FROM Posts this_) as query WHERE query.__hibernate_sort_row > 10 ORDER BY query.__hibernate_sort_row]
---> System.Data.SqlClient.SqlException: Must declare the scalar variable "@p1".
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
   at NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd)
   at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, Boolean autoDiscoverTypes, Boolean callable, RowSelection selection, ISessionImplementor session)
   at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)
   at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)
   at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)
   --- End of inner exception stack trace ---
   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.Impl.SessionImpl.List(CriteriaImpl criteria, IList results)
   at NHibernate.Impl.CriteriaImpl.List(IList results)
   at NHibernate.Impl.CriteriaImpl.List[T]()
   at CreateUpdateDDL.Program.Main(String[] args) in C:\dev\Advanced.NHibernate\CreateUpdateDDL\Program.cs:line 55

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://216.121.112.228/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira