title: Error: column "this_.phitorsionangle" must appear in the GROUP BY clause or be used in an aggregate function
body: <p>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:</p>
  
  <pre>
  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
  </pre>
  
  <p>But when I try it with Hibernate Criteria it fails. This is the code to build the query:</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>This is the error message I'm getting, with the full stacktrace below it:</p>
  
  <pre>
  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
  </pre>
  
  <p>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?</p>
  
  <p>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 :)</p>