[hibernate-issues] [JIRA] (HHH-15991) Hibernate fails when grouping by a related many-to-one entity

Mike Keller ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) *updated* an issue Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiNjk4NTg4ZTgw... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-15991?atlOrigin=eyJpIjoiNjk4NT... ) HHH-15991 ( https://hibernate.atlassian.net/browse/HHH-15991?atlOrigin=eyJpIjoiNjk4NT... ) Hibernate fails when grouping by a related many-to-one entity ( https://hibernate.atlassian.net/browse/HHH-15991?atlOrigin=eyJpIjoiNjk4NT... ) Change By: Mike Keller ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) *Problem* After upgrading to Spring Boot 3, Hibernate generates invalid sql statements on a JPQL that still worked with Spring Boot 2.7.5. See reproducer application here: [https://github.com/mkeller75/spring-boot-3-hibernate-group-by-reproducer|...] Here are the classes which still worked with Spring Boot 2.7.5 and did not work with Spring Boot 3.0: {code:java}package com.test.action.entity; import jakarta.persistence.Basic; import jakarta.persistence.Column; import jakarta.persistence.Id; import jakarta.persistence.MappedSuperclass; import jakarta.persistence.PrePersist; import jakarta.persistence.PreUpdate; import jakarta.persistence.Version; import lombok.AccessLevel; import lombok.AllArgsConstructor; import lombok.Getter; import lombok.NoArgsConstructor; import lombok.ToString; import lombok.experimental.SuperBuilder; import org.hibernate.annotations.JdbcTypeCode; import org.hibernate.type.SqlTypes; import java.time.OffsetDateTime; import java.util.Objects; import java.util.UUID; import static java.util.Objects.isNull; @SuperBuilder @Getter @NoArgsConstructor(access = AccessLevel.PUBLIC) @AllArgsConstructor(access = AccessLevel.PRIVATE) @MappedSuperclass @ToString(onlyExplicitlyIncluded = true) public class BaseEntity { @Id @Column(name = "ID") @Basic @JdbcTypeCode(SqlTypes.CHAR) @ToString.Include private UUID id; @Column(name = "SEQUENCE_NUMBER", nullable = false) @Version private Long sequenceNumber; @Column(name = "MODIFICATION_TIMESTAMP", nullable = false) private OffsetDateTime modificationTimestamp; @Column(name = "CREATION_TIMESTAMP", nullable = false) private OffsetDateTime creationTimestamp; @PreUpdate public void preUpdate() { modificationTimestamp = OffsetDateTime.now(); } @PrePersist public void prePersist() { if (isNull(this.id)) { this.id = UUID.randomUUID(); } if (sequenceNumber == null) { sequenceNumber = 0L; } if (creationTimestamp == null) { creationTimestamp = OffsetDateTime.now(); } preUpdate(); } @Override public boolean equals(Object o) { if (this == o) return true; if (o == null || getClass() != o.getClass()) return false; BaseEntity that = (BaseEntity) o; return id != null && Objects.equals(id, that.id); } @Override public int hashCode() { return id != null ? id.hashCode() : 0; } }{code} {code:java}package com.test.action.entity; import jakarta.persistence.CascadeType; import jakarta.persistence.Column; import jakarta.persistence.Entity; import jakarta.persistence.FetchType; import jakarta.persistence.JoinColumn; import jakarta.persistence.ManyToOne; import jakarta.persistence.Table; import lombok.Getter; import lombok.NoArgsConstructor; import lombok.Setter; import lombok.ToString; import lombok.experimental.SuperBuilder; import java.time.LocalDate; @Getter @ToString(callSuper = true, onlyExplicitlyIncluded = true) @SuperBuilder @Entity @Table(name = "ACT_ACTION") @NoArgsConstructor public class Action extends BaseEntity { @Column(name = "PARTNER_NUMBER", length = 50) private String partnerNumber; @Column(name = "TITLE", nullable = false) private String title; @Column(name = "FINDING", nullable = false) private String finding; @Column(name = "DEADLINE") @Setter private LocalDate deadline; @ManyToOne(fetch = FetchType.LAZY, cascade = {CascadeType.MERGE}) @JoinColumn(name = "FK_USER_ID") @Setter private User user; }{code} {code:java}package com.test.action.entity; import com.google.common.base.Joiner; import jakarta.persistence.CascadeType; import jakarta.persistence.Column; import jakarta.persistence.Entity; import jakarta.persistence.OneToMany; import jakarta.persistence.Table; import lombok.Getter; import lombok.NoArgsConstructor; import lombok.ToString; import lombok.experimental.SuperBuilder; import java.util.ArrayList; import java.util.List; import static org.apache.commons.lang3.StringUtils.isNotBlank; @Getter @ToString(callSuper = true, onlyExplicitlyIncluded = true) @SuperBuilder @Entity @Table(name = "ACT_USER") @NoArgsConstructor public class User extends BaseEntity { @Column(name = "PARTNER_NUMBER", length = 50) private String partnerNumber; @Column(name = "FIRSTNAME", length = 40) private String firstname; @Column(name = "LASTNAME", length = 40) private String lastname; @Column(name = "EMAIL", length = 128) private String email; @Column(name = "MOBILE", length = 30) private String mobile; @OneToMany(mappedBy = "user", cascade = CascadeType.ALL, orphanRemoval = true) private List<Action> actions = new ArrayList<>(); public boolean hasFirstnameLastname() { return isNotBlank(resolveFirstnameLastname()); } public String resolveFirstnameLastname() { return Joiner.on(" ").skipNulls().join(firstname, lastname); } public String resolveChannel() { if (mobile == null) { return email; } else { return mobile; } } }{code} {code:java}package com.test.action.entity.statistic; public interface Statistic { String getName(); String getKey(); Integer getCount(); String getAdditionalInfo(); }{code} {code:java}package com.test.action.entity.statistic; import com.test.action.entity.User; import lombok.AllArgsConstructor; import lombok.Builder; import lombok.ToString; import java.util.UUID; @ToString @Builder @AllArgsConstructor public class UserStatistic implements Statistic { private UUID userId; private String name; private String channel; private Long count; public UserStatistic(User user) { this(user, 0L); } public UserStatistic(User user, Long count) { this.userId = user != null ? user.getId() : null; this.name = user != null ? user.resolveFirstnameLastname() : null; this.channel = user != null && user.hasFirstnameLastname() ? user.resolveChannel() : null; this.count = count; } @Override public String getName() { return name; } @Override public String getKey() { return userId != null ? userId.toString() : null; } @Override public Integer getCount() { return count.intValue(); } @Override public String getAdditionalInfo() { return channel; } } {code} {code:java}package com.test.action.persistence; import com.test.action.entity.Action; import com.test.action.entity.statistic.UserStatistic; import org.springframework.data.jpa.repository.JpaRepository; import org.springframework.data.jpa.repository.Query; import org.springframework.data.repository.query.Param; import org.springframework.stereotype.Repository; import java.util.List; import java.util.UUID; @Repository public interface ActionRepository extends JpaRepository<Action, UUID> { @Query("SELECT new com.test.action.entity.statistic.UserStatistic(u, count(a))" + " FROM Action a INNER JOIN a.user u"+ " WHERE a.partnerNumber = :partnerNumber" + " GROUP BY u") List<UserStatistic> getUserStatistic(@Param("partnerNumber") String partnerNumber); } {code} With Spring Boot 3 we get the following error: {noformat}Caused by: org.hibernate.exception.SQLGrammarException: JDBC exception executing SQL [select u1_0.id,u1_0.creation_timestamp,u1_0.email,u1_0.firstname,u1_0.lastname,u1_0.mobile,u1_0.modification_timestamp,u1_0.partner_number,u1_0.sequence_number,count(a1_0.id) from act_action a1_0 join act_user u1_0 on u1_0.id=a1_0.fk_user_id where a1_0.partner_number=? group by a1_0.fk_user_id] at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:64) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:56) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95) at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:253) at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.getResultSet(DeferredResultSetAccess.java:146) at org.hibernate.sql.results.jdbc.internal.JdbcValuesResultSetImpl.lambda$processNext$0(JdbcValuesResultSetImpl.java:89) at org.hibernate.sql.results.jdbc.internal.JdbcValuesResultSetImpl.advance(JdbcValuesResultSetImpl.java:274) at org.hibernate.sql.results.jdbc.internal.JdbcValuesResultSetImpl.processNext(JdbcValuesResultSetImpl.java:85) at org.hibernate.sql.results.jdbc.internal.AbstractJdbcValues.next(AbstractJdbcValues.java:29) at org.hibernate.sql.results.internal.RowProcessingStateStandardImpl.next(RowProcessingStateStandardImpl.java:89) at org.hibernate.sql.results.spi.ListResultsConsumer.consume(ListResultsConsumer.java:142) at org.hibernate.sql.results.spi.ListResultsConsumer.consume(ListResultsConsumer.java:32) at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.doExecuteQuery(JdbcSelectExecutorStandardImpl.java:443) at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.executeQuery(JdbcSelectExecutorStandardImpl.java:166) at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.list(JdbcSelectExecutorStandardImpl.java:91) at org.hibernate.sql.exec.spi.JdbcSelectExecutor.list(JdbcSelectExecutor.java:31) at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.lambda$new$0(ConcreteSqmSelectQueryPlan.java:102) at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.withCacheableSqmInterpretation(ConcreteSqmSelectQueryPlan.java:305) at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.performList(ConcreteSqmSelectQueryPlan.java:246) at org.hibernate.query.sqm.internal.QuerySqmImpl.doList(QuerySqmImpl.java:546) at org.hibernate.query.spi.AbstractSelectionQuery.list(AbstractSelectionQuery.java:363) at org.hibernate.query.sqm.internal.QuerySqmImpl.list(QuerySqmImpl.java:1032) at org.hibernate.query.Query.getResultList(Query.java:94) at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:127) at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:90) at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:148) at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:136) at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:136) at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:120) at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:164) at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:143) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:77) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137) ... 170 common frames omitted Caused by: java.sql.SQLSyntaxErrorException: ORA-00979: not a GROUP BY expression at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:629) at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:563) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1150) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:770) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:298) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:497) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:151) at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:936) at oracle.jdbc.driver.OracleStatement.prepareDefineBufferAndExecute(OracleStatement.java:1171) at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1100) at oracle.jdbc.driver.OracleStatement.executeSQLSelect(OracleStatement.java:1425) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1308) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3745) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3854) at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1097) at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:217) ... 205 common frames omitted Caused by: oracle.jdbc.OracleDatabaseException: ORA-00979: not a GROUP BY expression{noformat} *Solution* To run it successfully with Spring Boot 3, the JPQL had to be adapted as follows, which is IMHO quite nasty and annoying to define the JOINS via JOIN ... ON and to list all attributes in the GROUP BY declaration: {code:java}    @Query("SELECT new com.test.action.entity.statistic.UserStatistic(u, count(a))" + " FROM Action a INNER JOIN User u ON a.user.id = u.id"+ " WHERE a.partnerNumber = :partnerNumber" + " GROUP BY u.id, u.sequenceNumber, u.modificationTimestamp, u.creationTimestamp, u.partnerNumber, u.firstname, u.lastname, u.email, u.mobile") List<UserStatistic> getUserStatistic(@Param("partnerNumber") String partnerNumber); {code} ( https://hibernate.atlassian.net/browse/HHH-15991#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-15991#add-comment?atlOrigin=ey... ) Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=Em... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100213- sha1:1fa7b87 )