Group by clause problem with postgresql jdbc 9.0-801

Hi,

I am having trouble about a query while using postgresql-9.0-801.jdbc4 on
Glassfish 3.1.1 whereas same query works on a DB tool (Navicat Lite) on same
database schema.

Query is being generated with criteria API which you will see at the end of
message.

I couldn't see a problem on the query, in fact, it gives results when I put
the parameters and run with Navicat Lite.

Could you please help about this problem?

Thanks,

Seckin

Logs and Exception:

Ps: function on create_timestamp would be date_part('day',
age(t1.create_timestamp)), trying to find out age in days, and group by with
same age result, not with create_timestamp

SEVERE:

Internal Exception: org.postgresql.util.PSQLException: ERROR: column
"t1.create_timestamp" must appear in the GROUP BY clause or be used in an
aggregate function

Position: 129

Error Code: 0

Call: SELECT t0.service_name, t0.departure_timestamp, t0.departure_station,
t0.arrival_station, t1.inventory_class, date_part(?,
age(t1.create_timestamp)), COUNT(t1.ID), AVG(t1.original_price) FROM segment
t0, item t1 WHERE ((((t1.product_type = ?) AND (t0.departure_timestamp >=
?)) AND (t1.inventory_class IN (?, ?))) AND (t0.ID = t1.segment_id)) GROUP
BY t0.service_name, t0.departure_timestamp, t0.departure_station,
t0.arrival_station, t1.inventory_class, date_part(?,
age(t1.create_timestamp))

bind => [6 parameters bound]

Query: ReportQuery(referenceClass=Item sql="SELECT t0.service_name,
t0.departure_timestamp, t0.departure_station, t0.arrival_station,
t1.inventory_class, date_part(?, age(t1.create_timestamp)), COUNT(t1.ID),
AVG(t1.original_price) FROM segment t0, item t1 WHERE ((((t1.product_type =
?) AND (t0.departure_timestamp >= ?)) AND (t1.inventory_class IN (?, ?)))
AND (t0.ID = t1.segment_id)) GROUP BY t0.service_name,
t0.departure_timestamp, t0.departure_station, t0.arrival_station,
t1.inventory_class, date_part(?, age(t1.create_timestamp))")

Local Exception Stack:

Exception [EclipseLink-4002] (Eclipse Persistence Services -
2.3.0.v20110604-r9504): org.eclipse.persistence.exceptions.DatabaseException

Internal Exception: org.postgresql.util.PSQLException: ERROR: column
"t1.create_timestamp" must appear in the GROUP BY clause or be used in an
aggregate function

Position: 129

Error Code: 0

Call: SELECT t0.service_name, t0.departure_timestamp, t0.departure_station,
t0.arrival_station, t1.inventory_class, date_part(?,
age(t1.create_timestamp)), COUNT(t1.ID), AVG(t1.original_price) FROM segment
t0, item t1 WHERE ((((t1.product_type = ?) AND (t0.departure_timestamp >=
?)) AND (t1.inventory_class IN (?, ?))) AND (t0.ID = t1.segment_id)) GROUP
BY t0.service_name, t0.departure_timestamp, t0.departure_station,
t0.arrival_station, t1.inventory_class, date_part(?,
age(t1.create_timestamp))

bind => [6 parameters bound]

Query: ReportQuery(referenceClass=Item sql="SELECT t0.service_name,
t0.departure_timestamp, t0.departure_station, t0.arrival_station,
t1.inventory_class, date_part(?, age(t1.create_timestamp)), COUNT(t1.ID),
AVG(t1.original_price) FROM segment t0, item t1 WHERE ((((t1.product_type =
?) AND (t0.departure_timestamp >= ?)) AND (t1.inventory_class IN (?, ?)))
AND (t0.ID = t1.segment_id)) GROUP BY t0.service_name,
t0.departure_timestamp, t0.departure_station, t0.arrival_station,
t1.inventory_class, date_part(?, age(t1.create_timestamp))")

at
org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseEx
ception.java:333)

at
org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecut
eCall(DatabaseAccessor.java:644)

Caused by: org.postgresql.util.PSQLException: ERROR: column
"t1.create_timestamp" must appear in the GROUP BY clause or be used in an
aggregate function

Position: 129

at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorI
mpl.java:2102)

at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.ja
va:1835)

at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)

at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.j
ava:500)

at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2St
atement.java:388)

at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statem
ent.java:273)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39
)

at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl
.java:25)

at java.lang.reflect.Method.invoke(Method.java:597)

at
org.postgresql.ds.jdbc23.AbstractJdbc23PooledConnection$StatementHandler.inv
oke(AbstractJdbc23PooledConnection.java:455)

at $Proxy238.executeQuery(Unknown Source)

at
com.sun.gjc.spi.jdbc40.PreparedStatementWrapper40.executeQuery(PreparedState
mentWrapper40.java:642)

at
org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeSele
ct(DatabaseAccessor.java:931)

at
org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecut
eCall(DatabaseAccessor.java:607)

... 96 more