JPA Criteria API fails on complex GROUP BY

Viewed 599

Given table that contains only primary key and DATETIME filed 'create_date' I'm trying to write query using JPA criteria api that will count number of rows created in different time intervals (ex. 2 hours, 12 hours, 3 days) starting from given date (agrument intervalStart in code below). In the future additional columns will added and user should be able to filter result by those columns - hence the need to build query dynamically. Query should work on PostgreSQL 9+

Entity class:

@Entity(name = "foo")
public class Foo {

    @Id
    @Column(name = "id", nullable = false)
    private long id;

    @Column(name = "create_date", columnDefinition = "TIMESTAMP")
    private LocalDateTime createDate;
}

Code that build the query (12 hour interval):

public class FooQueryFactory {

  private EntityManager entityManager;

  public CriteriaQuery<Tuple> buildQuery(LocalDateTime intervalStart) {
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Tuple> query = criteriaBuilder.createTupleQuery();

    Root<Foo> foo = query.from(Foo.class);

    Expression<Integer> dateRange = getDateRangeExpression(criteriaBuilder, foo, intervalStart);

    return query.multiselect(dateRange.alias("date_range"), criteriaBuilder.count(foo).alias("count"))
        .groupBy(dateRange)
        .orderBy(criteriaBuilder.asc(dateRange));
  }

  private Expression<Integer> getDateRangeExpression(CriteriaBuilder criteriaBuilder, Root<Foo> foo,
      LocalDateTime intervalStart) {
    long intervalLengthInSeconds = 12 * 60 * 60;
    return criteriaBuilder.function("floor", Integer.class,
        criteriaBuilder.quot(secondsSinceIntervalStart(criteriaBuilder, foo, intervalStart),
            criteriaBuilder.literal(intervalLengthInSeconds)));
  }

  private Expression<Long> secondsSinceIntervalStart(CriteriaBuilder criteriaBuilder, Root<Foo> foo,
      LocalDateTime from) {
    Expression<Long> createDateAsEpoch =
        criteriaBuilder.function("date_part", Long.class, criteriaBuilder.literal("epoch"), foo.get("createDate"));
    return criteriaBuilder.diff(getEpoch(from), createDateAsEpoch);
  }

  private long getEpoch(LocalDateTime dateTime) {
    return dateTime.atZone(ZoneId.ofOffset("UTC", ZoneOffset.ofHours(0))).toEpochSecond();
  }
}

Unfortunately, executing the query throws an exception:

javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet

    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154)
    at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1535)
    at org.hibernate.query.Query.getResultList(Query.java:165)
    at org.hibernate.query.criteria.internal.compile.CriteriaQueryTypeQueryAdapter.getResultList(CriteriaQueryTypeQueryAdapter.java:76)
    (...)
    Caused by: org.postgresql.util.PSQLException: ERROR: column "foo0_.create_date" must appear in the GROUP BY clause or be used in an aggregate function
  Position: 407
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2533)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2268)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:313)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:448)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:369)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:159)
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:109)
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57)
    ... 135 more

However, if I copy the created SQL to database console it executes successfully and produces expected results.

19:15:24.369 DEBUG [main] o.h.SQL - select floor((1497106800-date_part('epoch', foo0_.create_date))/7200) as col_0_0_, count(foo0_.id) as col_2_0_ from foo foo0_ group by floor((1497106800-date_part('epoch', foo0_.create_date))/7200) order by floor((1497106800-date_part(?, foo0_.create_date))/7200) asc
19:15:24.370 TRACE [main] o.h.t.d.s.BasicBinder - binding parameter [1] as [VARCHAR] - [epoch]
19:15:24.391 WARN  [main] o.h.e.j.s.SqlExceptionHelper - SQL Error: 0, SQLState: 42803
19:15:24.392 ERROR [main] o.h.e.j.s.SqlExceptionHelper - ERROR: column "foo0_.create_date" must appear in the GROUP BY clause or be used in an aggregate function
  Position: 407
0 Answers
Related