Hibernate version:
2.1.8
Code between sessionFactory.openSession() and session.close():
Code:
String query = "select p from Picture p join p.comments c group by p order by count(c) desc";
Query q = s.createQuery(query);
q.setMaxResults(howMany);
q.setCacheRegion("no.uka.intranett.query.gallery");
retList = q.list();
Full stack trace of any exception that occurs:
Quote:
2005-09-22 01:48:45,629 WARN JDBCExceptionReporter:57 - SQL Error: 0, SQLState: 42803
2005-09-22 01:48:45,630 ERROR JDBCExceptionReporter:58 - ERROR: column "picture0_.format" must appear in the GROUP BY clause or be used in an aggregate function
2005-09-22 01:48:45,633 WARN RequestProcessor:509 - Unhandled Exception thrown: class no.uka.intranett.db.DataResourceException
2005-09-22 01:48:45,653 ERROR InsertTag:920 - ServletException in '/secure/gallery/CreateBox.do?command=topCommented': net.sf.hibernate.exception.SQLGrammarException: Could not execute query
And some trace into various struts/tomcat stuff.
Name and version of the database you are using:PostgreSQL 7.4.7
The generated SQL (show_sql=true):Quote:
select picture0_.id as id, picture0_.format as format, picture0_.album as album, picture0_.comment as comment, picture0_.uploaded as uploaded, picture0_.uploader as uploader, picture0_.visi
ble as visible, (select avg(hot.rating) from gal_hotornot hot where hot.picture = picture0_.id) as f0_ from gal_pictures picture0_ inner join gal_comments comments1_ on picture0_.id=comments1_.picture group by picture0_.id , com
ments1_.id order by count(comments1_.id)desc limit ?
What have I missed in the HQL query? To me it looks quite similiar to the examples from the reference documentation, but I guess I've missed a detail since it gives bad SQL. Any ideas would be greatly appreciated.
Login
FAQ
Search
