Open
Description
Dorian Rudolph created an issue — 15th March 2016, 14:26:16:
When First is called on a GroupBy query invalid SQL code seems to be generated:
Console.WriteLine(session.Query<Employee>().GroupBy(x => x.Name).First());This causes the following Exception:
NHibernate.Exceptions.GenericADOException was unhandled HResult=-2146232832 Message=could not execute query < select TOP (1) employee0*.ID as ID0_, employee0_.Name as Name0_ from Employee employee0_ group by employee0*.Name > [SQL: select TOP (1) employee0*.ID as ID0_, employee0_.Name as Name0_ from Employee employee0_ group by employee0*.Name] Source=NHibernate SqlString=select TOP (1) employee0*.ID as ID0_, employee0_.Name as Name0_ from Employee employee0_ group by employee0*.Name StackTrace: bei NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters, IResultTransformer forcedResultTransformer) in c:\Projects\nhibernate-core\src\NHibernate\Loader\Loader.cs:Zeile 1668. bei NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in c:\Projects\nhibernate-core\src\NHibernate\Loader\Loader.cs:Zeile 1644. bei NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) in c:\Projects\nhibernate-core\src\NHibernate\Loader\Loader.cs:Zeile 1536. bei NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes) in c:\Projects\nhibernate-core\src\NHibernate\Loader\Loader.cs:Zeile 1531. bei NHibernate.Loader.Hql.QueryLoader.List(ISessionImplementor session, QueryParameters queryParameters) in c:\Projects\nhibernate-core\src\NHibernate\Loader\Hql\QueryLoader.cs:Zeile 289. bei NHibernate.Hql.Ast.ANTLR.QueryTranslatorImpl.List(ISessionImplementor session, QueryParameters queryParameters) in c:\Projects\nhibernate-core\src\NHibernate\Hql\Ast\ANTLR\QueryTranslatorImpl.cs:Zeile 110. bei NHibernate.Engine.Query.HQLQueryPlan.PerformList(QueryParameters queryParameters, ISessionImplementor session, IList results) in c:\Projects\nhibernate-core\src\NHibernate\Engine\Query\HQLQueryPlan.cs:Zeile 115. bei NHibernate.Impl.SessionImpl.List(IQueryExpression queryExpression, QueryParameters queryParameters, IList results) in c:\Projects\nhibernate-core\src\NHibernate\Impl\SessionImpl.cs:Zeile 634. bei NHibernate.Impl.AbstractSessionImpl.List(IQueryExpression queryExpression, QueryParameters parameters) in c:\Projects\nhibernate-core\src\NHibernate\Impl\AbstractSessionImpl.cs:Zeile 124. bei NHibernate.Impl.AbstractQueryImpl2.List() in c:\Projects\nhibernate-core\src\NHibernate\Impl\AbstractQueryImpl2.cs:Zeile 81. bei NHibernate.Linq.DefaultQueryProvider.ExecuteQuery(NhLinqExpression nhLinqExpression, IQuery query, NhLinqExpression nhQuery) in c:\Projects\nhibernate-core\src\NHibernate\Linq\DefaultQueryProvider.cs:Zeile 106. bei NHibernate.Linq.DefaultQueryProvider.Execute(Expression expression) in c:\Projects\nhibernate-core\src\NHibernate\Linq\DefaultQueryProvider.cs:Zeile 41. bei NHibernate.Linq.DefaultQueryProvider.Execute<TResult>(Expression expression) in c:\Projects\nhibernate-core\src\NHibernate\Linq\DefaultQueryProvider.cs:Zeile 46. bei System.Linq.Queryable.First<TSource>(IQueryable`1 source) bei NHibernateBasics.Program.Main() in C:\Users\dorud\Downloads\NHibernateBasics\NHibernateBasics\NHibernateBasics\Program.cs:Zeile 21. bei System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args) bei System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) bei Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() bei System.Threading.ThreadHelper.ThreadStart_Context(Object state) bei System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) bei System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) bei System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) bei System.Threading.ThreadHelper.ThreadStart() InnerException: System.Data.SqlClient.SqlException HResult=-2146232060 Message=Column 'Employee.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Source=.Net SqlClient Data Provider ErrorCode=-2146232060 Class=16 LineNumber=1 Number=8120 Procedure="" Server=(Local)\ENTW11 State=1 StackTrace: bei System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) bei System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) bei System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) bei System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) bei System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() bei System.Data.SqlClient.SqlDataReader.get_MetaData() bei System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) bei System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) bei System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) bei System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) bei System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) bei System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) bei System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader() bei NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd) in c:\Projects\nhibernate-core\src\NHibernate\AdoNet\AbstractBatcher.cs:Zeile 228. bei NHibernate.Loader.Loader.GetResultSet(IDbCommand st, Boolean autoDiscoverTypes, Boolean callable, RowSelection selection, ISessionImplementor session) in c:\Projects\nhibernate-core\src\NHibernate\Loader\Loader.cs:Zeile 1288. bei NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies, IResultTransformer forcedResultTransformer) in c:\Projects\nhibernate-core\src\NHibernate\Loader\Loader.cs:Zeile 436. bei NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies, IResultTransformer forcedResultTransformer) in c:\Projects\nhibernate-core\src\NHibernate\Loader\Loader.cs:Zeile 252. bei NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters, IResultTransformer forcedResultTransformer) in c:\Projects\nhibernate-core\src\NHibernate\Loader\Loader.cs:Zeile 1659. InnerException:The same error is also causes with FirstOrDefault, SingleOrDefault, Single.
A different exception is thrown when an additional Select statement is added:
// Some comments here Console.WriteLine(session.Query<Employee>().GroupBy(x => x.Name).Select(x => x).First());System.NotImplementedException was unhandled HResult=-2147467263 Message=Die Methode oder der Vorgang ist nicht implementiert. Source=NHibernate StackTrace: bei NHibernate.Linq.GroupBy.NonAggregatingGroupByRewriter.FlattenSubQuery(SubQueryExpression subQueryExpression, QueryModel queryModel) in c:\Projects\nhibernate-core\src\NHibernate\Linq\GroupBy\NonAggregatingGroupByRewriter.cs:Zeile 58. bei NHibernate.Linq.GroupBy.NonAggregatingGroupByRewriter.ReWrite(QueryModel queryModel) in c:\Projects\nhibernate-core\src\NHibernate\Linq\GroupBy\NonAggregatingGroupByRewriter.cs:Zeile 33. bei NHibernate.Linq.Visitors.QueryModelVisitor.GenerateHqlQuery(QueryModel queryModel, VisitorParameters parameters, Boolean root) in c:\Projects\nhibernate-core\src\NHibernate\Linq\Visitors\QueryModelVisitor.cs:Zeile 32. bei NHibernate.Linq.NhLinqExpression.Translate(ISessionFactoryImplementor sessionFactory, Boolean filter) in c:\Projects\nhibernate-core\src\NHibernate\Linq\NhLinqExpression.cs:Zeile 67. bei NHibernate.Hql.Ast.ANTLR.ASTQueryTranslatorFactory.CreateQueryTranslators(IQueryExpression queryExpression, String collectionRole, Boolean shallow, IDictionary`2 filters, ISessionFactoryImplementor factory) in c:\Projects\nhibernate-core\src\NHibernate\Hql\Ast\ANTLR\ASTQueryTranslatorFactory.cs:Zeile 24. bei NHibernate.Engine.Query.QueryExpressionPlan.CreateTranslators(IQueryExpression queryExpression, String collectionRole, Boolean shallow, IDictionary`2 enabledFilters, ISessionFactoryImplementor factory) in c:\Projects\nhibernate-core\src\NHibernate\Engine\Query\QueryExpressionPlan.cs:Zeile 32. bei NHibernate.Engine.Query.QueryExpressionPlan..ctor(IQueryExpression queryExpression, Boolean shallow, IDictionary`2 enabledFilters, ISessionFactoryImplementor factory) in c:\Projects\nhibernate-core\src\NHibernate\Engine\Query\QueryExpressionPlan.cs:Zeile 13. bei NHibernate.Engine.Query.QueryPlanCache.GetHQLQueryPlan(IQueryExpression queryExpression, Boolean shallow, IDictionary`2 enabledFilters) in c:\Projects\nhibernate-core\src\NHibernate\Engine\Query\QueryPlanCache.cs:Zeile 67. bei NHibernate.Impl.AbstractSessionImpl.GetHQLQueryPlan(IQueryExpression queryExpression, Boolean shallow) in c:\Projects\nhibernate-core\src\NHibernate\Impl\AbstractSessionImpl.cs:Zeile 412. bei NHibernate.Impl.AbstractSessionImpl.CreateQuery(IQueryExpression queryExpression) in c:\Projects\nhibernate-core\src\NHibernate\Impl\AbstractSessionImpl.cs:Zeile 372. bei NHibernate.Linq.DefaultQueryProvider.PrepareQuery(Expression expression, IQuery& query, NhLinqExpression& nhQuery) in c:\Projects\nhibernate-core\src\NHibernate\Linq\DefaultQueryProvider.cs:Zeile 73. bei NHibernate.Linq.DefaultQueryProvider.Execute(Expression expression) in c:\Projects\nhibernate-core\src\NHibernate\Linq\DefaultQueryProvider.cs:Zeile 39. bei NHibernate.Linq.DefaultQueryProvider.Execute<TResult>(Expression expression) in c:\Projects\nhibernate-core\src\NHibernate\Linq\DefaultQueryProvider.cs:Zeile 46. bei System.Linq.Queryable.First<TSource>(IQueryable`1 source) bei NHibernateBasics.Program.Main() in C:\Users\dorud\Downloads\NHibernateBasics\NHibernateBasics\NHibernateBasics\Program.cs:Zeile 21. bei System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args) bei System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) bei Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() bei System.Threading.ThreadHelper.ThreadStart_Context(Object state) bei System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) bei System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) bei System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) bei System.Threading.ThreadHelper.ThreadStart() InnerException:However, calling ToList does not cause an exception:
Console.WriteLine(session.Query<Employee>().GroupBy(x => x.Name).Select(x => x).ToList().First());This would generate the following SQL code:
select employee0*.ID as ID0_, employee0_.Name as Name0_ from Employee employee0*Using Take causes the same error as First:
Console.WriteLine(session.Query<Employee>().GroupBy(x => x.Name).Select(x => x).Take(1).ToList().First());
Dorian Rudolph added a comment — 16th March 2016, 9:03:38:
Calling OrderBy on a GroupBy query yields yet another exception:
var t = session.Query<Employee>().GroupBy(x => x.Name).OrderBy(x => x.Key).ToList();NHibernate.HibernateException was unhandled HResult=-2146232832 Message=Query Source could not be identified: ItemName = x, ItemType = System.Linq.IGrouping`2<System.String,NHibernateBasics.Employee>, Expression = from IGrouping`2 x in {value(NHibernate.Linq.NhQueryable`1<NHibernateBasics.Employee>) => GroupBy(<x>.Name, [x])} Source=NHibernate StackTrace: bei NHibernate.Linq.QuerySourceNamer.GetName(IQuerySource querySource) in c:\Projects\nhibernate-core\src\NHibernate\Linq\QuerySourceNamer.cs:Zeile 30. bei NHibernate.Linq.Visitors.HqlGeneratorExpressionTreeVisitor.VisitQuerySourceReferenceExpression(QuerySourceReferenceExpression expression) in c:\Projects\nhibernate-core\src\NHibernate\Linq\Visitors\HqlGeneratorExpressionTreeVisitor.cs:Zeile 200. bei NHibernate.Linq.Visitors.HqlGeneratorExpressionTreeVisitor.VisitExpression(Expression expression) in c:\Projects\nhibernate-core\src\NHibernate\Linq\Visitors\HqlGeneratorExpressionTreeVisitor.cs:Zeile 116. bei NHibernate.Linq.Visitors.HqlGeneratorExpressionTreeVisitor.VisitMemberExpression(MemberExpression expression) in c:\Projects\nhibernate-core\src\NHibernate\Linq\Visitors\HqlGeneratorExpressionTreeVisitor.cs:Zeile 381. bei NHibernate.Linq.Visitors.HqlGeneratorExpressionTreeVisitor.VisitExpression(Expression expression) in c:\Projects\nhibernate-core\src\NHibernate\Linq\Visitors\HqlGeneratorExpressionTreeVisitor.cs:Zeile 92. bei NHibernate.Linq.Visitors.HqlGeneratorExpressionTreeVisitor.Visit(Expression expression, VisitorParameters parameters) in c:\Projects\nhibernate-core\src\NHibernate\Linq\Visitors\HqlGeneratorExpressionTreeVisitor.cs:Zeile 23. bei NHibernate.Linq.Visitors.QueryModelVisitor.VisitOrderByClause(OrderByClause orderByClause, QueryModel queryModel, Int32 index) in c:\Projects\nhibernate-core\src\NHibernate\Linq\Visitors\QueryModelVisitor.cs:Zeile 258. bei Remotion.Linq.Clauses.OrderByClause.Accept(IQueryModelVisitor visitor, QueryModel queryModel, Int32 index) bei Remotion.Linq.QueryModelVisitorBase.VisitBodyClauses(ObservableCollection`1 bodyClauses, QueryModel queryModel) bei Remotion.Linq.QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel) bei NHibernate.Linq.Visitors.QueryModelVisitor.Visit() in c:\Projects\nhibernate-core\src\NHibernate\Linq\Visitors\QueryModelVisitor.cs:Zeile 122. bei NHibernate.Linq.Visitors.QueryModelVisitor.GenerateHqlQuery(QueryModel queryModel, VisitorParameters parameters, Boolean root) in c:\Projects\nhibernate-core\src\NHibernate\Linq\Visitors\QueryModelVisitor.cs:Zeile 68. bei NHibernate.Linq.NhLinqExpression.Translate(ISessionFactoryImplementor sessionFactory, Boolean filter) in c:\Projects\nhibernate-core\src\NHibernate\Linq\NhLinqExpression.cs:Zeile 67. bei NHibernate.Hql.Ast.ANTLR.ASTQueryTranslatorFactory.CreateQueryTranslators(IQueryExpression queryExpression, String collectionRole, Boolean shallow, IDictionary`2 filters, ISessionFactoryImplementor factory) in c:\Projects\nhibernate-core\src\NHibernate\Hql\Ast\ANTLR\ASTQueryTranslatorFactory.cs:Zeile 24. bei NHibernate.Engine.Query.QueryExpressionPlan.CreateTranslators(IQueryExpression queryExpression, String collectionRole, Boolean shallow, IDictionary`2 enabledFilters, ISessionFactoryImplementor factory) in c:\Projects\nhibernate-core\src\NHibernate\Engine\Query\QueryExpressionPlan.cs:Zeile 32. bei NHibernate.Engine.Query.QueryExpressionPlan..ctor(IQueryExpression queryExpression, Boolean shallow, IDictionary`2 enabledFilters, ISessionFactoryImplementor factory) in c:\Projects\nhibernate-core\src\NHibernate\Engine\Query\QueryExpressionPlan.cs:Zeile 13. bei NHibernate.Engine.Query.QueryPlanCache.GetHQLQueryPlan(IQueryExpression queryExpression, Boolean shallow, IDictionary`2 enabledFilters) in c:\Projects\nhibernate-core\src\NHibernate\Engine\Query\QueryPlanCache.cs:Zeile 67. bei NHibernate.Impl.AbstractSessionImpl.GetHQLQueryPlan(IQueryExpression queryExpression, Boolean shallow) in c:\Projects\nhibernate-core\src\NHibernate\Impl\AbstractSessionImpl.cs:Zeile 412. bei NHibernate.Impl.AbstractSessionImpl.CreateQuery(IQueryExpression queryExpression) in c:\Projects\nhibernate-core\src\NHibernate\Impl\AbstractSessionImpl.cs:Zeile 372. bei NHibernate.Linq.DefaultQueryProvider.PrepareQuery(Expression expression, IQuery& query, NhLinqExpression& nhQuery) in c:\Projects\nhibernate-core\src\NHibernate\Linq\DefaultQueryProvider.cs:Zeile 73. bei NHibernate.Linq.DefaultQueryProvider.Execute(Expression expression) in c:\Projects\nhibernate-core\src\NHibernate\Linq\DefaultQueryProvider.cs:Zeile 39. bei NHibernate.Linq.DefaultQueryProvider.Execute<TResult>(Expression expression) in c:\Projects\nhibernate-core\src\NHibernate\Linq\DefaultQueryProvider.cs:Zeile 46. bei Remotion.Linq.QueryableBase`1.GetEnumerator() bei System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) bei System.Linq.Enumerable.ToList<TSource>(IEnumerable`1 source) bei NHibernateBasics.Program.Main() in C:\Users\dorud\Downloads\NHibernateBasics\NHibernateBasics\NHibernateBasics\Program.cs:Zeile 21. bei System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args) bei System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) bei Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() bei System.Threading.ThreadHelper.ThreadStart_Context(Object state) bei System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) bei System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) bei System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) bei System.Threading.ThreadHelper.ThreadStart() InnerException:
lnu added a comment — 24th March 2016, 9:05:46:
related to https://nhibernate.jira.com/browse/NH-3027?