Hi,

I just ran into an exception while migrating an application from Mysql to Postgresql. It happens in the following query:

final String QUERY =
"select o from " + Order.class.getName() + " as o, " +
PrintJob.class.getName() + " as p" +
" where o.item.info.designID = ?" +
" and p.status != ?" +
" group by o.objId" +
" order by o.orderDate desc";
try {
Session s = session.open();
Object[] args = {label, PrintJob.STATUS_DONE};
Type[] types = {Hibernate.STRING, Hibernate.STRING};
return s.find( QUERY, args, types );
} ...


The code leads to the following exception while using Postgresql, however, it works just fine under Mysql. What am I missing? Any hints appreciated.


-tom

2004-02-20 11:32:16,646 ERROR [http8080-Processor4] (JDBCExceptionReporter.java:46): ERROR: Attribute order0_.deliveryaddress must be GROUPed or used in an aggregate function
2004-02-20 11:32:16,651 ERROR [http8080-Processor4] (JDBCException.java:38): Could not execute query
java.sql.SQLException: ERROR: Attribute order0_.deliveryaddress must be GROUPed or used in an aggregate function
at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131)
at org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection.java:505)
at org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:320)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:48)
at org.postgresql.jdbc1.AbstractJdbc1Statement.executeQuery(AbstractJdbc1Statement.java:153)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:83)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:794)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:188)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:132)
at net.sf.hibernate.loader.Loader.doList(Loader.java:949)
at net.sf.hibernate.loader.Loader.list(Loader.java:940)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:833)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1475)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1454)
at de.oew.ocp.hibernate.DomainStore.findPendingOrdersForDesign(DomainStore.java:240)

BTW:

public class Order {
private CalendarModel item;
private int quantity;
private User user;
private Date orderDate;
private Date deliveryDate;
private PaymentInfo paymentInfo;
private Address deliveryAddress;
private Set printJobs = new HashSet();

/**
* @hibernate.set
* lazy="true"
* cascade="all"
* inverse="true"
* @hibernate.collection-key
* column="order_id"
* @hibernate.collection-one-to-many
* column="pjob_id"
* class="de.oew.ocp.print.PrintJob"
*/
public Set getPrintJobs() {
return printJobs;
}

/**
* @hibernate.many-to-one
* cascade="save-update"
*/
public Address getDeliveryAddress() {
return deliveryAddress;
}

/**
* @hibernate.id
* generator-class="native"
* unsaved-value="null"
* column="id"
*/
public Integer getObjId() {
return objId;
}
}

public class PrintJob {
private String status = STATUS_PENDING;
private String barcode;
private int barcodeSuffix;
private Order order;
// yada yada yada
}

Thanks, that works.

Is that link still valid? I couldn't find any helpful there (and there is no #A12 anchor in that page).