Grouping by JPA and PostgreSQL 9.0

I am trying to use a group in a JPA request. Let's say I have a class Teacher

and a class Student

. A Teacher

may have more Student

, or Student

may have only one Teacher

(one for many).

The following JPA request:

Query q = this.em.createQuery(  "SELECT teacher, COUNT(student)" +
                                " FROM StudentJpa student" +
                                " JOIN student.teacher teacher" +
                                " GROUP BY teacher" +
                                " ORDER BY COUNT(student) DESC");

      

        
        
        
      

    

Creates the following SQL query:

select
        teacherjpa1_.teacher_id as col_0_0_,
        count(studentjpa0_.id) as col_1_0_,
        teacherjpa1_.teacher_id as teacher1_0_,
        teacherjpa1_.name as name0_ 
    from
        student studentjpa0_ 
    inner join
        teacher teacherjpa1_ 
            on studentjpa0_.teacher_id=teacherjpa1_.teacher_id 
    group by
        teacherjpa1_.teacher_id 
    order by
        count(studentjpa0_.id) DESC

      

        
        
        
      

    

In PostgreSQL 9.0, I am getting the following error:

org.postgresql.util.PSQLException: ERROR: column "teacherjpa1_.name" must appear in GROUP BY clause or used in aggregate function

The same error does not appear in PostgreSQL 9.1.

Can anyone explain to me why? JPA seems to generate the group in the wrong way: it must include all attributes Teacher

, not just the ID.

This is my JPA / Hibernate / DB config if needed:

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd http://www.springframework.org/schema/tx  http://www.springframework.org/schema/tx/spring-tx-3.0.xsd">
    <context:property-placeholder location="/WEB-INF/jdbc.properties" />

    <bean id="dataSource" class="org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy">
        <constructor-arg>
            <bean class="org.springframework.jdbc.datasource.DriverManagerDataSource">
                <property name="driverClassName" value="org.postgresql.Driver" />
                <property name="url" value="${db.url}" />
                <property name="username" value="${db.username}" />
                <property name="password" value="${db.password}" />
            </bean>
        </constructor-arg>
    </bean>

    <bean id="jpaAdapter" class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
        <property name="databasePlatform" value="org.hibernate.dialect.PostgreSQLDialect" />
        <property name="showSql" value="${db.showSql}" />
        <property name="generateDdl" value="${db.generateDdl}" />
    </bean>

    <!-- enabling annotation driven configuration /-->
    <context:annotation-config />
    <context:component-scan base-package="my.package" />

    <!-- Instructs the container to look for beans with @Transactional and decorate them -->
    <tx:annotation-driven transaction-manager="transactionManager" proxy-target-class="true" />

    <!-- FactoryBean that creates the EntityManagerFactory  -->
    <bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
        <property name="jpaVendorAdapter" ref="jpaAdapter" />
        <property name="jpaProperties">
            <props>
                <prop key="hibernate.format_sql">true</prop>
                <prop key="hibernate.hbm2ddl.auto">update</prop>
            </props>
        </property>
        <property name="dataSource" ref="dataSource" />
    </bean>

    <!-- A transaction manager for working with JPA EntityManagerFactories -->
    <bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
        <property name="entityManagerFactory" ref="entityManagerFactory" />
    </bean>
</beans>

      

        
        
        
      

    

Thank!

Refresh . The solution is to quote GROUP BY teacher.id, teacher.name

instead GROUP BY teacher

, but this is not very convenient. Is there a better solution?