[hibernate-issues] [Hibernate-JIRA] Created: (HHH-6596) SQLServer2008Dialect: Problem with pageing. Aggregate function in group by

[Ralph Schaer (JIRA)]

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