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

[Fabio M. (JIRA) <nh...@gm...>]

     [ http://216.121.112.228/browse/NH-2360?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Fabio Maulo closed NH-2360.
---------------------------

    Resolution: Obsolete

> 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/nhibernate-blog-model (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