SQLServer2008Dialect: Problem with pageing. Aggregate function in group by -------------------------------------------------------------------------- Key: HHH-6596 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-6596 Project: Hibernate Core Issue Type: Bug Components: entity-manager Affects Versions: 3.6.7 Environment: Hibernate 3.6.7.Final, Jtds 1.2.5, MS Sql Server 2008R2, Windows 7 x64, org.hibernate.dialect.SQLServer2008Dialect Reporter: Ralph Schaer Priority: Minor CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery<Long> query = builder.createQuery(Long.class); Root<Employee> root = query.from(Employee.class); query.select(builder.count(root)).distinct(true); Long result = em.createQuery(query).getSingleResult(); These statements create the following sql query. This query does not work because group by contains an aggregate. The statements are working with org.hibernate.dialect.SQLServerDialect. Removing the distinct(true) also works but is not an option because this code is in a third party library. Hibernate: WITH query AS (select ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __hibernate_row_nr__, count(employee0_.employeeid) as col_0_0_ from employee employee0_ group by count(employee0_.employeeid)) SELECT * FROM query WHERE __hibernate_row_nr__ BETWEEN ? AND ? 14:10:03.482 [main] WARN o.h.util.JDBCExceptionReporter - SQL Error: 144, SQLState: S1000 14:10:03.485 [main] ERROR o.h.util.JDBCExceptionReporter - Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause. Exception in thread "main" javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute query at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1215) at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1148) at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:307) at org.hibernate.ejb.criteria.CriteriaQueryCompiler$3.getSingleResult(CriteriaQueryCompiler.java:264) Caused by: org.hibernate.exception.GenericJDBCException: could not execute query at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:140) at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:128) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) at org.hibernate.loader.Loader.doList(Loader.java:2536) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276) at org.hibernate.loader.Loader.list(Loader.java:2271) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:452) at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:363) at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1268) at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102) at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:274) ... 2 more Caused by: java.sql.SQLException: Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause. at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368) at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820) at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258) @Entity public class Employee { @Id @GeneratedValue(strategy = GenerationType.AUTO) private long employeeId; @NotBlank @Length(max = 30) private String employeeName; @NotBlank @Length(max = 30) private String employeeSurname; @Length(max = 50) private String job; public long getEmployeeId() { return employeeId; } public void setEmployeeId(long employeeId) { this.employeeId = employeeId; } public String getEmployeeName() { return employeeName; } public void setEmployeeName(String employeeName) { this.employeeName = employeeName; } public String getEmployeeSurname() { return employeeSurname; } public void setEmployeeSurname(String employeeSurname) { this.employeeSurname = employeeSurname; } public String getJob() { return job; } public void setJob(String job) { this.job = job; } } -- This message is automatically generated by JIRA. For more information on JIRA, see: http://www.atlassian.com/software/jira