Re: Group by clause problem with postgresql jdbc 9.0-801

It would be interesting to see the server logs to see how the server
interpreted this query.

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On Wed, Sep 7, 2011 at 2:45 PM, Seckin Pulatkan
<seckinpulatkan(at)hotmail(dot)com> wrote:
> 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