Column "X" must appear in the GROUP BY clause or be used in an aggregate function

[filpano]

I am using Spring Boot 3.2.4 with Spring Data JPA. I have the following JpaRepository with a native query against PostgreSQL 15:

@Repository
public interface RevenueRepository extends JpaRepository<MyValue, UUID> {

    @Query(value = """
            SELECT site_id, date_trunc('day', hour - (INTERVAL '1 hour') * :offset) AS hour, name,
                   SUM(line_totals) AS line_totals, SUM(sum_quantity) AS sum_quantity
            FROM service.revenue
            WHERE site_id = :siteId AND hour >= :from AND hour < :until AND name IN (:names)
            GROUP BY site_id, date_trunc('day', hour - (INTERVAL '1 hour') * :offset), name
            ORDER BY 2 ASC, 3 ASC;
            """, nativeQuery = true)
    Stream<MyValue>
    findBySiteIdAndHourBetweenAndNameInGroupedByDayWithOffset(@Param("siteId") UUID siteId,
                                                              @Param("names") List<String> names,
                                                              @Param("from") Instant from,
                                                              @Param("until") Instant until,
                                                              @Param("offset") int offset);

When this is run, I get the following exception:

Caused by: org.hibernate.exception.SQLGrammarException: JDBC exception executing SQL [/* dynamic native SQL query */ SELECT site_id, date_trunc('day', hour - (INTERVAL '1 hour') * ?) AS hour, name,
       SUM(line_totals) AS line_totals, SUM(sum_quantity) AS sum_quantity
FROM service.revenue
WHERE site_id = ? AND hour >= ? AND hour < ? AND name IN (?,?)
GROUP BY site_id, date_trunc('day', hour - (INTERVAL '1 hour') * ?), name
ORDER BY 2 ASC, 3 ASC;
] [ERROR: column "revenue.hour" must appear in the GROUP BY clause or be used in an aggregate function
  Position: 66] [n/a]

At first I thought that this might be because of the naming of my hour column: according to the PostgreSQL Appendix, it is not a reserved keyword and only requires aliasing (as I have done here).

If I run this query in e.g. psql, it works as expected, hence I believe this to be either a bug in in Spring Data JPA's handling of native queries or perhaps even a problem with Hibernate itself. I haven't debugged this exhaustively.

The current workaround is to refer to the grouping column by index, i.e.:

WHERE ...
GROUP BY site_id, 2, name
ORDER BY 2 ASC, 3 ASC;

which works as expected.

I can try to get a minimal reproducible example up and running this weekend. I imagine it won't be very hard to reproduce: use a native query that contains function with an alias for a column in a GROUP BY and see what happens.

Full stacktrace of the relevant JPA code:

Caused by: org.hibernate.exception.SQLGrammarException: JDBC exception executing SQL [/* dynamic native SQL query */ SELECT site_id, date_trunc('day', hour - (INTERVAL '1 hour') * ?) AS hour, name,
       SUM(line_totals) AS line_totals, SUM(sum_quantity) AS sum_quantity
FROM service.revenue
WHERE site_id = ? AND hour >= ? AND hour < ? AND name IN (?,?)
GROUP BY site_id, date_trunc('day', hour - (INTERVAL '1 hour') * ?), name
ORDER BY 2 ASC, 3 ASC;
] [ERROR: column "revenue.hour" must appear in the GROUP BY clause or be used in an aggregate function
  Position: 66] [n/a]
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:91)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:58)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:108)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:94)
    at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:265)
    at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.getResultSet(DeferredResultSetAccess.java:167)
    at org.hibernate.sql.results.jdbc.internal.AbstractResultSetAccess.getMetaData(AbstractResultSetAccess.java:36)
    at org.hibernate.sql.results.jdbc.internal.AbstractResultSetAccess.getColumnCount(AbstractResultSetAccess.java:52)
    at org.hibernate.query.results.ResultSetMappingImpl.resolve(ResultSetMappingImpl.java:193)
    at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.resolveJdbcValuesSource(JdbcSelectExecutorStandardImpl.java:325)
    at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.doExecuteQuery(JdbcSelectExecutorStandardImpl.java:115)
    at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.executeQuery(JdbcSelectExecutorStandardImpl.java:83)
    at org.hibernate.sql.exec.spi.JdbcSelectExecutor.scroll(JdbcSelectExecutor.java:96)
    at org.hibernate.query.sql.internal.NativeSelectQueryPlanImpl.performScroll(NativeSelectQueryPlanImpl.java:181)
    at org.hibernate.query.sql.internal.NativeQueryImpl.doScroll(NativeQueryImpl.java:829)
    at org.hibernate.query.spi.AbstractSelectionQuery.scroll(AbstractSelectionQuery.java:531)
    at org.hibernate.query.spi.AbstractSelectionQuery.stream(AbstractSelectionQuery.java:548)
    at org.hibernate.query.spi.AbstractSelectionQuery.getResultStream(AbstractSelectionQuery.java:542)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:568)
    at org.springframework.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:281)
    at org.springframework.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:265)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution$StreamExecution.doExecute(JpaQueryExecution.java:391)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:92)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:149)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:137)
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:170)
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:158)
    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:70)
    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:392)
    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)
    ... 13 more
Caused by: org.postgresql.util.PSQLException: ERROR: column "revenue.hour" must appear in the GROUP BY clause or be used in an aggregate function
  Position: 66
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2725)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2412)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:371)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:502)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:419)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:194)
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:137)
    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:246)
    ... 48 more

[christophstrobl]

Thank you for getting in touch. The given Stacktrace indicates that this is a problem within PG. A reproducer would certainly help. Please make sure to not only run the query via a spring-data repository, but also using plain EntityManager#createNativeQuery. Thank you!

[spring-projects-issues]

If you would like us to look at this issue, please provide the requested information. If the information is not provided within the next 7 days this issue will be closed.

[spring-projects-issues]

Closing due to lack of requested feedback. If you would like us to look at this issue, please provide the requested information and we will re-open the issue.