Menu
▾
▴
nhibernate-issues
|
From: Josh R. (JIRA) <nh...@gm...> - 2010-10-03 13:52:34
|
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
|
|
From: Julian M. (JIRA) <nh...@gm...> - 2010-10-05 03:41:41
|
[ http://216.121.112.228/browse/NH-2360?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Julian Maughan updated NH-2360:
-------------------------------
Description:
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
was:
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
Updated Blog sample URL
> 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
|
|
From: Julian M. (JIRA) <nh...@gm...> - 2010-10-05 03:47:40
|
[ http://216.121.112.228/browse/NH-2360?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=19968#action_19968 ]
Julian Maughan commented on NH-2360:
------------------------------------
I notice Ayende's Blog sample uses NH 2.1.2.GA.
I would suggest trying the latest Alpha release (3.0.0-Alpha3), as this fixes a number of parameter indexing and paging issues. If this does answer your question, could you please let me know, so we can close this ticket.
> 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
|
|
From: Josh R. (JIRA) <nh...@gm...> - 2010-10-06 03:59:55
|
[ http://216.121.112.228/browse/NH-2360?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=19971#action_19971 ]
Josh Robb commented on NH-2360:
-------------------------------
I downloaded 3.0.0-Alpha3 and this works perfectly! Thanks for that.
j.
> 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
|
|
From: Fabio M. (JIRA) <nh...@gm...> - 2011-06-20 11:21:13
|
[ 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
|
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X