I'm trying to execute the following query:

select avg(mark.score * rating.ratingQuality) as avg1, avg(rating.ratingQuality) as avg2
from Mark as mark
join mark.rating as rating
join mark.rating.product as product
where product.id = ? and rating.approved = " + Rating.APPROVED +
and rating.creationDate >= ? and rating.creationDate < ?
group by mark.id

I'm binding the following array: new Object[] {softwareId, startDate, endDate}. The passed values are fine but I always get a QueryException saying "expected in select". There may be something wrong with my query (even if I can't see what) but why an IN would be needed?

Here are more details:

Hibernate version: 2.1.3

Full stack trace of any exception that occurs:
net.sf.hibernate.QueryException: , expected in SELECT [select avg(mark.score * rating.ratingQuality) as avg1, avg(rating.ratingQuality) as avg2 from com.chooss.model.product.Mark as mark join mark.rating as rating join mark.rating.product as product where product.id = ? and rating.approved = 1 and rating.creationDate >= ? and rating.creationDate < ? ]
at net.sf.hibernate.hql.SelectParser.token(SelectParser.java:153)
at net.sf.hibernate.hql.ClauseParser.token(ClauseParser.java:87)
at net.sf.hibernate.hql.ClauseParser.end(ClauseParser.java:114)
at net.sf.hibernate.hql.PreprocessingParser.end(PreprocessingParser.java:143)
at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:30)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:149)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:138)
at net.sf.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:293)
at net.sf.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1554)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1525)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
at org.springframework.orm.hibernate.HibernateTemplate$25.doInHibernate(HibernateTemplate.java:546)
at org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:243)
at org.springframework.orm.hibernate.HibernateTemplate.executeFind(HibernateTemplate.java:263)
at org.springframework.orm.hibernate.HibernateTemplate.find(HibernateTemplate.java:535)
at org.springframework.orm.hibernate.HibernateTemplate.find(HibernateTemplate.java:527)
at com.chooss.persistence.product.hibernate.SoftwareDAOHibernate.getGlobalAverage(SoftwareDAOHibernate.java:88)
at com.chooss.service.product.impl.SoftwareManagerImpl.getAverage(SoftwareManagerImpl.java:190) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

Name and version of the database you are using:
MySQL 4.1

Thanks a lot.

Do you have mapping files for each of the objects? I once had that problem and I had not yet registered my mapping files in the hibernate.cfg.xml file.

Yes, classes Mark, Rating and Product all have a registered mapping file (checked it at least 3 times).

Are operations in select authorised?

Err, did you realize it actually says



(note the comma)

Yes but can't find why, as you can see there's no comma in the where clause.

Yes. Try running your query against the real database and see what it says. Also you should perhaps update your Hibernate version first before doing further testing.

Try removing the alias for all the aggregate functions in your query. That should solve the issue. I faced the same issue, but resolved it after removing the alias.