Group by clause problem with postgresql jdbc 9.0-801 - Mailing list pgsql-jdbc

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(DatabaseException.java:333)

      at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(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(QueryExecutorImpl.java:2102)

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

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

      at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)

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

      at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.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.invoke(AbstractJdbc23PooledConnection.java:455)

      at $Proxy238.executeQuery(Unknown Source)

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

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

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

      ... 96 more