I'm having some trouble with an sql query. I'm using Hibernate Criteria to build the query. I create some bins from a database by rounding the values with certain intervals (the binSize) and then grouping them. This works great when I try it directly in SQL with the query:
SELECT floor(phiTorsionAngle / 2) * 2 as phiTorsionAngleBin,
floor(psiTorsionAngle / 2) * 2 as psiTorsionAngleBin,
floor(phiTorsionAngle / 2) + 180 as phiTorsionAngleBinIndex,
floor(psiTorsionAngle / 2) + 180 as psiTorsionAngleBinIndex,
count(*) as numberOfResidues
FROM residue
WHERE phitorsionangle IS NOT NULL
AND psitorsionangle IS NOT NULL
GROUP BY phiTorsionAngleBin, psiTorsionAngleBin
But when I try it with Hibernate Criteria it fails. This is the code to build the query:
ScrollableResults phiPsiBins = createCriteria()
.setProjection(Projections.projectionList()
.add(Projections.sqlGroupProjection(
"floor(phiTorsionAngle / " + binSize + ") * " + binSize + " as phiTorsionAngleBin, " +
"floor(psiTorsionAngle / " + binSize + ") * " + binSize + " as psiTorsionAngleBin, " +
"floor(phiTorsionAngle / " + binSize + ") + 180 as phiTorsionAngleBinIndex, " +
"floor(psiTorsionAngle / " + binSize + ") + 180 as psiTorsionAngleBinIndex, " +
"count(*) as numberOfResidues",
"phiTorsionAngleBin, psiTorsionAngleBin",
new String[] {"phiTorsionAngleBin", "psiTorsionAngleBin", "phiTorsionAngleBinIndex", "psiTorsionAngleBinIndex", "numberOfResidues"},
new Type[] {Hibernate.DOUBLE, Hibernate.DOUBLE, Hibernate.INTEGER, Hibernate.INTEGER, Hibernate.INTEGER})))
.add(Restrictions.isNotNull("phiTorsionAngle"))
.add(Restrictions.isNotNull("psiTorsionAngle"))
.setResultTransformer(Transformers.aliasToBean(PhiPsiBinResult.class))
.setCacheMode(CacheMode.IGNORE)
.scroll(ScrollMode.FORWARD_ONLY);
This is the error message I'm getting, with the full stacktrace below it:
ERROR: column "this_.phitorsionangle" must appear in the GROUP BY clause or be used in an aggregate function WARN 2011-01-11 16:13:43,047 main JDBCExceptionReporter:100 - SQL Error: 0, SQLState: 42803 ERROR 2011-01-11 16:13:43,047 main JDBCExceptionReporter:101 - ERROR: column "this_.phitorsionangle" must appear in the GROUP BY clause or be used in an aggregate function Position: 143 Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not execute query using scroll at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) at org.hibernate.loader.Loader.scroll(Loader.java:2340) at org.hibernate.loader.criteria.CriteriaLoader.scroll(CriteriaLoader.java:113) at org.hibernate.impl.SessionImpl.scroll(SessionImpl.java:1561) at org.hibernate.impl.CriteriaImpl.scroll(CriteriaImpl.java:320) at nl.ru.cmbi.pdbeter.core.controller.DAO.ResidueDAO.getPhiPsiBinSet(ResidueDAO.java:236) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:616) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309) at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202) at $Proxy27.getPhiPsiBinSet(Unknown Source) at nl.ru.cmbi.pdbeter.statistics.controller.StatisticsFunctions.makeRamachandranPlot(StatisticsFunctions.java:26) at nl.ru.cmbi.pdbeter.statistics.controller.StatisticsMain.start(StatisticsMain.java:39) at nl.ru.cmbi.pdbeter.statistics.controller.StatisticsMain.main(StatisticsMain.java:33) Caused by: org.postgresql.util.PSQLException: ERROR: column "this_.phitorsionangle" must appear in the GROUP BY clause or be used in an aggregate function Position: 143 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:367) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:271) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208) at org.hibernate.loader.Loader.getResultSet(Loader.java:1812) at org.hibernate.loader.Loader.scroll(Loader.java:2305) ... 18 more
I tried adding the phiTorsionAngle and psiTorsionAngle to the GROUP BY, and this seems to work, but it makes no sense to do that. I don't want to group by every possible value for phiTorsionAngle, I want to group by the entire bin, probably containing a lot of different values for phiTorsionAngle. Why does it give me this error and how do I work around it?
UPDATE: I didn't actually try to let the query run until it was finished yet, and I got a Java out of heap space error, because it tried to load about 19 million entries, so adding the phiTorsionAngle and psiTorsionAngle to the grouping is now entirely out of the question :)
