From: | Seckin Pulatkan <seckinpulatkan(at)hotmail(dot)com> |
---|---|
To: | <pgsql-jdbc(at)postgresql(dot)org> |
Subject: | Group by clause problem with postgresql jdbc 9.0-801 |
Date: | 2011-09-07 18:45:16 |
Message-ID: | BAY162-ds16112E6F645563B014A6C1CE1F0@phx.gbl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(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
From | Date | Subject | |
---|---|---|---|
Next Message | Dave Cramer | 2011-09-07 21:17:15 | Re: Group by clause problem with postgresql jdbc 9.0-801 |
Previous Message | Kris Jurka | 2011-09-07 16:30:01 | Re: Patch: Some more state codes |