[hibernate-issues] [Hibernate-JIRA] Created: (HHH-6596) SQLServer2008Dialect: Problem with pageing. Aggregate function in group by
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