spring-data-jpa icon indicating copy to clipboard operation
spring-data-jpa copied to clipboard

Published 20 hours ago • verified publisher icon spring-projects

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

Open filpano opened this issue 10 months ago • 2 comments

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

filpano avatar Apr 16 '24 15:04 filpano

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!

christophstrobl avatar Apr 17 '24 06:04 christophstrobl

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 avatar Apr 24 '24 06:04 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.

spring-projects-issues avatar May 01 '24 06:05 spring-projects-issues

Sorry for the late feedback.

I've created an example repository where this problem can be seen: https://github.com/filpano/jpa_exception_demo/tree/main

There are tests for both the JpaRepository as well as the EntityManager#createNativeQuery approach - both yield the same exception (which might hint that it's actuall a problem with the PG Driver, as you mention).

The project uses Java 17 as well as the following DM:

id 'org.springframework.boot' version '3.2.8'
id 'io.spring.dependency-management' version '1.1.6'

filpano avatar Aug 02 '24 15:08 filpano