Difference in executing query via Hibernate and PostgreSQL leading to PSQLException

Question

I would like to execute a native SQL query via JPA 2.0 with Hibernate in version 4.2.21 on a Postgres 9.4 database system.

Basically, according to my latest post on stackoverflow, I try to put a large number of objects/records into "temporal" buckets.

The setup can be simplified to the following setup containing a table "MyObject" with an id field and a given timestamp:

CREATE TABLE myobject
(
  id bigint NOT NULL,
  lastseen timestamp without time zone, 
)

My piece of code, which should execute the query is this one:

Query q = getEntityManager().createNativeQuery(
             "select count(id),date_part('day', :startDate - c.lastseen)  AS " +
             "difference from myobject c " +
             "group by date_part('day', :startDate - c.lastseen) order by difference asc");

q.setParameter("startDate", startDate);

List<Object[]> rawResults = q.getResultList();

//process the reuslts

Executing this query with a sample date via pgAdmin3 returns the result as expected.

However, if I try to execute the same query via Hibernate as a native query it fails with the following exception:

Caused by: org.postgresql.util.PSQLException:
FEHLER: column „myobject.lastseen“ must appear in the group by clause or be used in an aggregate function
  Position: 40
  at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2270)
  at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1998)
  at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
  at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:570)
  at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:420)
  at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:305)
  at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:79)
  ... 94 more

This exception seems valid and self-explaining but why can I execute the same query via PgAdmin3? Is the Hibernate SQL parser more strict than pgAdmin3 or does it compensate some mistake?

So how can my SQL query be formulated in order to make it executable via Hibernate?

EDIT:

For some reason the following SQL statement (with explicit sub-select) works via PgAdmin3 as well as via Hibernate:

select count(id), difference
from (select c.id,c.lastseen,date_part('day', :startDate - c.lastseen) AS difference
      from myobject c) AS temporalBucket
group by difference
order by difference asc

But this still does not answer the question for the previous query in the given code fragment.

Answer 1

This query should work as well, without subquery:

SELECT count(id)  -- or even better: count(*) AS ct
     , date_part('day', :startDate - c.lastseen) AS difference
FROM   myobject c 
GROUP  BY difference
ORDER  BY difference;

The reason I suspect: Hibernate uses prepared statements and the two occurrences of :startDate are passed as two parameters. This way, Postgres cannot assume that both expressions (in the SELECT list and in GROUP BY) are the same ...

Demonstrating with the equivalent SQL command PREPARE, this works:

PREPARE test1 AS
SELECT count(*) AS ct
     , date_part('day', $1 - c.lastseen) AS difference
FROM   myobject c 
GROUP  BY date_part('day', $1 - c.lastseen)
ORDER  BY difference;

While this does not:

PREPARE test2 AS
SELECT count(*) AS ct
     , date_part('day', $1 - c.lastseen) AS difference
FROM   myobject c 
GROUP  BY date_part('day', $2 - c.lastseen)
ORDER  BY difference;

.. and raises the same exception as you show.

You can avoid the problem a priori with the query I suggested.

Related:

• PostgreSQL Where count condition