Обсуждение: 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@hotmail.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

Could this be the difference between

cqdb=# prepare foo(int, int) as select x / $1 from generate_series(1,10) x group by x / $2; execute foo(3,3);
ERROR:  column "x.x" must appear in the GROUP BY clause or be used in an aggregate function
ERROR:  prepared statement "foo" does not exist

and

cqdb=# select x / 3 from generate_series(1,10) x group by x / 3;
 ?column?
----------
        2
        0
        3
        1
(4 rows)

?

This could be the case if Navicat is inlining the parameters. Unfortunately, I can't think of a good suggestion for a fix if this is indeed the case (except that the generated queries should group by the columns of interest, rather than repeating the expressions, but that's probably not an easy fix)...

---
Maciek Sakrejda | System Architect | Truviso

1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com

On Wed, Sep 7, 2011 at 10:55 PM, Maciek Sakrejda <msakrejda@truviso.com> wrote:
> Could this be the difference between
>
> cqdb=# prepare foo(int, int) as select x / $1 from generate_series(1,10) x
> group by x / $2; execute foo(3,3);
> ERROR:  column "x.x" must appear in the GROUP BY clause or be used in an
> aggregate function
> ERROR:  prepared statement "foo" does not exist
>
> and
>
> cqdb=# select x / 3 from generate_series(1,10) x group by x / 3;
>  ?column?
> ----------
>         2
>         0
>         3
>         1
> (4 rows)
>
> ?
>
> This could be the case if Navicat is inlining the parameters. Unfortunately,
> I can't think of a good suggestion for a fix if this is indeed the case
> (except that the generated queries should group by the columns of interest,
> rather than repeating the expressions, but that's probably not an easy
> fix)...

Yes, agreed.

The server error message is clear from the messages shown, nothing more needed.

The problem is that
  date_part(?, age(t1.create_timestamp))

in the SELECT caluse does not match
  date_part(?, age(t1.create_timestamp))

in the GROUP BY clause, because the presence of parameter markers
means that they are potentially different expressions.

Try re-writing the query like this:

* In SELECT clause write
  date_part(?, age(t1.create_timestamp)) AS expression1

* In GROUP BY clause write
  expression1 instead of date_part(?, age(t1.create_timestamp))

The SQL parser will then recognise the GROUP BY correctly.

I think we could regard this as a JDBC annoyance, but we're not
parsing the SQL at that point so it's got no way to know the two
parameter markers ("?") would be the same. I'm sure the same problem
would exist in JDBC with other RDBMS also, since its a problem caused
by unnamed parameter markers.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Thanks for replies.
I've tried giving alias with criteria api before posting the message but
couldn't see on the query in the logs.
I will work more on that and let you know about the result.

Thanks,

Seckin

-----Oorspronkelijk bericht-----
Van: Simon Riggs [mailto:simon@2ndQuadrant.com]
Verzonden: 08 September 2011 08:01
Aan: Maciek Sakrejda
CC: Dave Cramer; Seckin Pulatkan; pgsql-jdbc
Onderwerp: Re: [JDBC] Group by clause problem with postgresql jdbc 9.0-801

On Wed, Sep 7, 2011 at 10:55 PM, Maciek Sakrejda <msakrejda@truviso.com>
wrote:
> Could this be the difference between
>
> cqdb=# prepare foo(int, int) as select x / $1 from generate_series(1,10) x
> group by x / $2; execute foo(3,3);
> ERROR:  column "x.x" must appear in the GROUP BY clause or be used in an
> aggregate function
> ERROR:  prepared statement "foo" does not exist
>
> and
>
> cqdb=# select x / 3 from generate_series(1,10) x group by x / 3;
>  ?column?
> ----------
>         2
>         0
>         3
>         1
> (4 rows)
>
> ?
>
> This could be the case if Navicat is inlining the parameters.
Unfortunately,
> I can't think of a good suggestion for a fix if this is indeed the case
> (except that the generated queries should group by the columns of
interest,
> rather than repeating the expressions, but that's probably not an easy
> fix)...

Yes, agreed.

The server error message is clear from the messages shown, nothing more
needed.

The problem is that
  date_part(?, age(t1.create_timestamp))

in the SELECT caluse does not match
  date_part(?, age(t1.create_timestamp))

in the GROUP BY clause, because the presence of parameter markers
means that they are potentially different expressions.

Try re-writing the query like this:

* In SELECT clause write
  date_part(?, age(t1.create_timestamp)) AS expression1

* In GROUP BY clause write
  expression1 instead of date_part(?, age(t1.create_timestamp))

The SQL parser will then recognise the GROUP BY correctly.

I think we could regard this as a JDBC annoyance, but we're not
parsing the SQL at that point so it's got no way to know the two
parameter markers ("?") would be the same. I'm sure the same problem
would exist in JDBC with other RDBMS also, since its a problem caused
by unnamed parameter markers.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

I converted my code to native query version and then query worked
successfully.
Thank you for pointing out the problem and your suggestions.

Kind Regards,

Seckin

-----Oorspronkelijk bericht-----
Van: Simon Riggs [mailto:simon@2ndQuadrant.com]
Verzonden: 08 September 2011 08:01
Aan: Maciek Sakrejda
CC: Dave Cramer; Seckin Pulatkan; pgsql-jdbc
Onderwerp: Re: [JDBC] Group by clause problem with postgresql jdbc 9.0-801

On Wed, Sep 7, 2011 at 10:55 PM, Maciek Sakrejda <msakrejda@truviso.com>
wrote:
> Could this be the difference between
>
> cqdb=# prepare foo(int, int) as select x / $1 from generate_series(1,10) x
> group by x / $2; execute foo(3,3);
> ERROR:  column "x.x" must appear in the GROUP BY clause or be used in an
> aggregate function
> ERROR:  prepared statement "foo" does not exist
>
> and
>
> cqdb=# select x / 3 from generate_series(1,10) x group by x / 3;
>  ?column?
> ----------
>         2
>         0
>         3
>         1
> (4 rows)
>
> ?
>
> This could be the case if Navicat is inlining the parameters.
Unfortunately,
> I can't think of a good suggestion for a fix if this is indeed the case
> (except that the generated queries should group by the columns of
interest,
> rather than repeating the expressions, but that's probably not an easy
> fix)...

Yes, agreed.

The server error message is clear from the messages shown, nothing more
needed.

The problem is that
  date_part(?, age(t1.create_timestamp))

in the SELECT caluse does not match
  date_part(?, age(t1.create_timestamp))

in the GROUP BY clause, because the presence of parameter markers
means that they are potentially different expressions.

Try re-writing the query like this:

* In SELECT clause write
  date_part(?, age(t1.create_timestamp)) AS expression1

* In GROUP BY clause write
  expression1 instead of date_part(?, age(t1.create_timestamp))

The SQL parser will then recognise the GROUP BY correctly.

I think we could regard this as a JDBC annoyance, but we're not
parsing the SQL at that point so it's got no way to know the two
parameter markers ("?") would be the same. I'm sure the same problem
would exist in JDBC with other RDBMS also, since its a problem caused
by unnamed parameter markers.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services