Hi,

I have a problem with a hql query containing the group by clause. It works in hibernate console but not in my spring webapp (tomcat). I use hibernate 2.1.7 with postgresql 7.4. The mapping documents are all generated with middlegen. Here is the hql query :

select count(*),
sum(sp.malespecimen + sp.femalespecimen + sp.unsexedspecimen),
loc.originalcountry,
hp.hostplantfamily,
hp.latinname,
hp.commonnameenglish,
rel.rank,
rel.description
from Specimen as sp
inner join sp.locality as loc
inner join sp.hostplant as hp
inner join sp.reliability as rel
where sp.taxon.id = 21
group by loc.originalcountry, hp.hostplantfamily, hp.latinname, hp.commonnameenglish, rel.rank, rel.description


Here is the error message :

[fruitfly] WARN [http-8080-Processor25] JDBCExceptionReporter.logExceptions(57) | SQL Error: 0, SQLState: 42803
[fruitfly] ERROR [http-8080-Processor25] JDBCExceptionReporter.logExceptions(58) | ERROR: column "originalco4_.id" must
appear in the GROUP BY clause or be used in an aggregate function

[fruitfly] WARN [http-8080-Processor25] JDBCExceptionReporter.logExceptions(57) | SQL Error: 0, SQLState: 42803
[fruitfly] ERROR [http-8080-Processor25] JDBCExceptionReporter.logExceptions(58) | ERROR: column "originalco4_.id" must
appear in the GROUP BY clause or be used in an aggregate function

net.sf.hibernate.exception.SQLGrammarException: Could not execute query
at net.sf.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:59)
at net.sf.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:30)
at net.sf.hibernate.impl.SessionImpl.convert(SessionImpl.java:4110)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1556)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
at net.gbif.be.fruitfly.dao.hibernate.mrac_fruitfly.SpecimenDAOHibernate.getSpecimenSets(SpecimenDAOHibernate.ja
va:66)
at net.gbif.be.fruitfly.service.impl.mrac_fruitfly.TaxonManagerImpl.getTaxonInfo(TaxonManagerImpl.java:80)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:296)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:
154)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:121)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:56)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:143)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:174)
at $Proxy21.getTaxonInfo(Unknown Source)
at net.gbif.be.fruitfly.webapp.action.mrac_fruitfly.TaxonInfoController.handle(TaxonInfoController.java:39)
at org.springframework.web.servlet.mvc.AbstractCommandController.handleRequestInternal(AbstractCommandController
.java:79)
at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:128)
at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java
:44)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:584)
at org.springframework.web.servlet.FrameworkServlet.serviceWrapper(FrameworkServlet.java:366)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:317)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at com.opensymphony.module.sitemesh.filter.PageFilter.parsePage(PageFilter.java:118)
at com.opensymphony.module.sitemesh.filter.PageFilter.doFilter(PageFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at net.gbif.be.fruitfly.webapp.filter.GZIPFilter.doFilter(GZIPFilter.java:50)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.displaytag.filter.ResponseOverrideFilter.doFilter(ResponseOverrideFilter.java:125)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:75)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.springframework.orm.hibernate.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.ja
va:171)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:825)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:731)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:526)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Thread.java:595)
Caused by: org.postgresql.util.PSQLException: ERROR: column "originalco4_.id" must appear in the GROUP BY clause or be u
sed in an aggregate function

at org.postgresql.util.PSQLException.parseServerError(PSQLException.java:139)
at org.postgresql.core.QueryExecutor.executeV3(QueryExecutor.java:154)
at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:101)
at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:43)
at org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:515)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:50)
at org.postgresql.jdbc1.AbstractJdbc1Statement.executeQuery(AbstractJdbc1Statement.java:231)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:88)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:875)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:269)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.doList(Loader.java:1033)
at net.sf.hibernate.loader.Loader.list(Loader.java:1024)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:854)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1553)
... 55 more


select originalco4_.id as id0_, hostplantf5_.id as id1_, originalco4_.name as name0_, hostplantf5_.name as na
me1_, count(*) as x0_0_, sum(specimen0_.malespecimen+specimen0_.femalespecimen+specimen0_.unsexedspecimen) as x1_0_, ori
ginalco4_.id as x2_0_, hostplantf5_.id as x3_0_, hostplant2_.latinname as x4_0_, hostplant2_.commonnameenglish as x5_0_,
reliabilit3_.rank as x6_0_, reliabilit3_.description as x7_0_ from mrac_fruitfly.specimen specimen0_ inner join mrac_fr
uitfly.locality locality1_ on specimen0_.locality_id=locality1_.id inner join mrac_fruitfly.hostplant hostplant2_ on spe
cimen0_.hostplant_id=hostplant2_.id inner join mrac_fruitfly.reliability reliabilit3_ on specimen0_.reliability_id=relia
bilit3_.id, mrac_fruitfly.originalcountry originalco4_, mrac_fruitfly.hostplantfamily hostplantf5_ where locality1_.orig
inalcountry_id=originalco4_.id and hostplant2_.hostplantfamily_id=hostplantf5_.id and ((specimen0_.taxon_id=? )) group b
y locality1_.originalcountry_id , hostplant2_.hostplantfamily_id , hostplant2_.latinname , hostplant2_.commonnameenglis
h , reliabilit3_.rank , reliabilit3_.description

[fruitfly] DEBUG [http-8080-Processor24] SessionImpl.find(1536) | find:

select count(*),
sum(sp.malespecimen + sp.femalespecimen + sp.unsexedspecimen),
loc.originalcountry,
hp.hostplantfamily,
hp.latinname,
hp.commonnameenglish,
rel.rank,
rel.description
from Specimen as sp
inner join sp.locality as loc
inner join sp.hostplant as hp
inner join sp.reliability as rel
where sp.taxon.id = :taxonId
group by loc.originalcountry, hp.hostplantfamily, hp.latinname, hp.commonnameenglish, rel.rank, rel.desc
ription


[fruitfly] DEBUG [http-8080-Processor24] QueryParameters.traceParameters(112) | named parameters: {taxonId=21}
[fruitfly] DEBUG [http-8080-Processor24] QueryTranslator.compile(147) | compiling query
[fruitfly] DEBUG [http-8080-Processor24] QueryTranslator.logQuery(207) | HQL:

select count(*),
sum(sp.malespecimen + sp.femalespecimen + sp.unsexedspecimen),
loc.originalcountry,
hp.hostplantfamily,
hp.latinname,
hp.commonnameenglish,
rel.rank,
rel.description
from net.gbif.be.fruitfly.model.mrac_fruitfly.Specimen as sp
inner join sp.locality as loc
inner join sp.hostplant as hp
inner join sp.reliability as rel
where sp.taxon.id = :taxonId
group by loc.originalcountry, hp.hostplantfamily, hp.latinname, hp.commonnameenglish, rel.rank, rel.desc
ription


[fruitfly] DEBUG [http-8080-Processor24] QueryTranslator.logQuery(208) | SQL: select originalco4_.id as id0_, hostplantf
5_.id as id1_, originalco4_.name as name0_, hostplantf5_.name as name1_, count(*) as x0_0_, sum(specimen0_.malespecimen+
specimen0_.femalespecimen+specimen0_.unsexedspecimen) as x1_0_, originalco4_.id as x2_0_, hostplantf5_.id as x3_0_, host
plant2_.latinname as x4_0_, hostplant2_.commonnameenglish as x5_0_, reliabilit3_.rank as x6_0_, reliabilit3_.description
as x7_0_ from mrac_fruitfly.specimen specimen0_ inner join mrac_fruitfly.locality locality1_ on specimen0_.locality_id=
locality1_.id inner join mrac_fruitfly.hostplant hostplant2_ on specimen0_.hostplant_id=hostplant2_.id inner join mrac_f
ruitfly.reliability reliabilit3_ on specimen0_.reliability_id=reliabilit3_.id, mrac_fruitfly.originalcountry originalco4
_, mrac_fruitfly.hostplantfamily hostplantf5_ where locality1_.originalcountry_id=originalco4_.id and hostplant2_.hostpl
antfamily_id=hostplantf5_.id and ((specimen0_.taxon_id=? )) group by locality1_.originalcountry_id , hostplant2_.hostpl
antfamily_id , hostplant2_.latinname , hostplant2_.commonnameenglish , reliabilit3_.rank , reliabilit3_.description
[fruitfly] DEBUG [http-8080-Processor24] BatcherImpl.logOpenPreparedStatement(203) | about to open: 0 open PreparedState
ments, 0 open ResultSets
[fruitfly] DEBUG [http-8080-Processor24] BatcherImpl.log(229) | select originalco4_.id as id0_, hostplantf5_.id as id1_,
originalco4_.name as name0_, hostplantf5_.name as name1_, count(*) as x0_0_, sum(specimen0_.malespecimen+specimen0_.fem
alespecimen+specimen0_.unsexedspecimen) as x1_0_, originalco4_.id as x2_0_, hostplantf5_.id as x3_0_, hostplant2_.latinn
ame as x4_0_, hostplant2_.commonnameenglish as x5_0_, reliabilit3_.rank as x6_0_, reliabilit3_.description as x7_0_ from
mrac_fruitfly.specimen specimen0_ inner join mrac_fruitfly.locality locality1_ on specimen0_.locality_id=locality1_.id
inner join mrac_fruitfly.hostplant hostplant2_ on specimen0_.hostplant_id=hostplant2_.id inner join mrac_fruitfly.reliab
ility reliabilit3_ on specimen0_.reliability_id=reliabilit3_.id, mrac_fruitfly.originalcountry originalco4_, mrac_fruitf
ly.hostplantfamily hostplantf5_ where locality1_.originalcountry_id=originalco4_.id and hostplant2_.hostplantfamily_id=h
ostplantf5_.id and ((specimen0_.taxon_id=? )) group by locality1_.originalcountry_id , hostplant2_.hostplantfamily_id ,
hostplant2_.latinname , hostplant2_.commonnameenglish , reliabilit3_.rank , reliabilit3_.description
Hibernate: select originalco4_.id as id0_, hostplantf5_.id as id1_, originalco4_.name as name0_, hostplantf5_.name as na
me1_, count(*) as x0_0_, sum(specimen0_.malespecimen+specimen0_.femalespecimen+specimen0_.unsexedspecimen) as x1_0_, ori
ginalco4_.id as x2_0_, hostplantf5_.id as x3_0_, hostplant2_.latinname as x4_0_, hostplant2_.commonnameenglish as x5_0_,
reliabilit3_.rank as x6_0_, reliabilit3_.description as x7_0_ from mrac_fruitfly.specimen specimen0_ inner join mrac_fr
uitfly.locality locality1_ on specimen0_.locality_id=locality1_.id inner join mrac_fruitfly.hostplant hostplant2_ on spe
cimen0_.hostplant_id=hostplant2_.id inner join mrac_fruitfly.reliability reliabilit3_ on specimen0_.reliability_id=relia
bilit3_.id, mrac_fruitfly.originalcountry originalco4_, mrac_fruitfly.hostplantfamily hostplantf5_ where locality1_.orig
inalcountry_id=originalco4_.id and hostplant2_.hostplantfamily_id=hostplantf5_.id and ((specimen0_.taxon_id=? )) group b
y locality1_.originalcountry_id , hostplant2_.hostplantfamily_id , hostplant2_.latinname , hostplant2_.commonnameenglis
h , reliabilit3_.rank , reliabilit3_.description

[fruitfly] DEBUG [http-8080-Processor24] BatcherImpl.getPreparedStatement(252) | preparing statement
[fruitfly] DEBUG [http-8080-Processor24] NullableType.nullSafeSet(46) | binding '21' to parameter: 1
[fruitfly] DEBUG [http-8080-Processor24] JDBCExceptionReporter.logExceptions(49) | SQL Exception

[fruitfly] DEBUG [http-8080-Processor24] BatcherImpl.logClosePreparedStatement(210) | done closing: 0 open PreparedState
ments, 0 open ResultSets
[fruitfly] DEBUG [http-8080-Processor24] BatcherImpl.closePreparedStatement(272) | closing statement
[fruitfly] DEBUG [http-8080-Processor24] JDBCExceptionReporter.logExceptions(49) | Could not execute query

[fruitfly] DEBUG [http-8080-Processor24] JDBCTransaction.commit(59) | commit
[fruitfly] DEBUG [http-8080-Processor24] SessionImpl.afterTransactionCompletion(594) | transaction completion
[fruitfly] DEBUG [http-8080-Processor24] JDBCTransaction.toggleAutoCommit(103) | re-enabling autocommit
[fruitfly] DEBUG [http-8080-Processor24] SessionImpl.close(576) | closing session
[fruitfly] DEBUG [http-8080-Processor24] SessionImpl.disconnect(3371) | disconnecting session
[fruitfly] DEBUG [http-8080-Processor24] SessionImpl.afterTransactionCompletion(594) | transaction completion


Thanks in advance,

Johan