PostgreSQL query works in pgAdmin but not in Java EclipseLink

Question

I have a SQL that counts rows per date truncate (months, days, hours). Think a history graph. Query works fine if executed in pgAdmin but fails in Java using EclipseLink.

pgAdmin query:

SELECT date_trunc( 'hour', delivered_at ),
       COUNT(date_trunc( 'hour', delivered_at )) AS num 
FROM messages 
WHERE channel_type='EMAIL' 
  AND created_at>='2016-02-28 16:01:08.882' 
  AND created_at<='2016-02-29 16:01:08.882' 
GROUP BY date_trunc( 'hour', delivered_at );

JPQL Named query

SELECT FUNCTION('date_trunc', 'hour', m.deliveredAt ),
       COUNT(FUNCTION('date_trunc', 'hour', m.deliveredAt )) AS num 
 FROM Message m 
 WHERE m.channelType = :channelType 
   AND m.createdAt >= :fromDate 
   AND m.createdAt <= :toDate 
 GROUP BY FUNCTION('date_trunc', 'hour', m.deliveredAt )

EclipseLink debugging log:

SELECT date_trunc(?, delivered_at), COUNT(date_trunc(?, delivered_at)) FROM messages 
WHERE (((channel_type = ?) AND (created_at >= ?)) AND (created_at <= ?)) GROUP BY date_trunc(?, delivered_at)
bind => [hour, hour, EMAIL, 2015-12-27 00:00:00.0, 2015-12-27 00:00:00.0, hour]

Error:

ERROR: column "messages.delivered_at" must appear in the GROUP BY clause or be used in an aggregate function Position: 23

PostgreSQL log:

2016-03-01 13:22:08 CET ERROR: column "messages.delivered_at" must appear in the GROUP BY clause or be used in an aggregate function at character 23 2016-03-01 13:22:08 CET STATEMENT: SELECT date_trunc($1, delivered_at), COUNT(delivered_at) FROM messages WHERE (((channel_type = $2) AND (created_at >= $3)) AND (created_at <= $4)) GROUP BY date_trunc($5, delivered_at) 2016-03-01 13:22:08 CET LOG: execute S_2: SELECT 1

If I replace the binded variables from EclipseLink logged query and execute it in pgAdmin the query works. What is going on here?

Edit: Just to clarify, it also works using em.createNativeQuery.

Answer 1

PostgreSQL can have trouble with resolving parameter binding, which in this case manifests as native SQL with parameters inline work, while JPA generated SQL which defaults to bound parameters fails.

One solution is to turn off parameter binding by passing "eclipselink.jdbc.bind-parameters" with a value of "false" as either a query hint for the specific query, or as a persistence unit property to turn off parameter binding by default for all queries.