I'm executing an Hibernate query with an operation in the select clause:

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

The query gets executed without any problem. I also tested the corresponding generated query in MySQL and it worked. Yet when Hibernate tries to extract the result set, it tries to fetch an alias name that doesn't exist. See below:

Hibernate version: 2.1.8

Full stack trace of any exception that occurs:

[chooss] WARN [http-8080-Processor24] JDBCExceptionReporter.logExceptions(57) | SQL Error: 0, SQLState: S0022
[chooss] ERROR [http-8080-Processor24] JDBCExceptionReporter.logExceptions(58) | Column 'x2_0_' not found.
[chooss] WARN [http-8080-Processor24] JDBCExceptionReporter.logExceptions(57) | SQL Error: 0, SQLState: S0022
[chooss] ERROR [http-8080-Processor24] JDBCExceptionReporter.logExceptions(58) | Column 'x2_0_' not found.
[chooss] WARN [http-8080-Processor24] SQLErrorCodeSQLExceptionTranslator.translate(279) | Unable to translate SQLException with errorCode '0', will now try the fallback translator
[chooss] DEBUG [http-8080-Processor24] PropertyMessageResources.<init>(127) | Initializing, config='org.apache.struts.action.LocalStrings', returnNull=true
[chooss] ERROR [http-8080-Processor24] ActionExceptionHandler.logException(135) | org.springframework.jdbc.BadSqlGrammarException: Bad SQL grammar [] in task 'Hibernate operation'; nested exception is java.sql.SQLException: Column 'x2_0_' not found.
java.sql.SQLException: Column 'x2_0_' not found.
at com.mysql.jdbc.ResultSet.findColumn(ResultSet.java:2316)
at com.mysql.jdbc.ResultSet.getFloat(ResultSet.java:1234)
at org.apache.commons.dbcp.DelegatingResultSet.getFloat(DelegatingResultSet.java:242)
at net.sf.hibernate.type.FloatType.get(FloatType.java:16)
at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:62)
at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:53)
at net.sf.hibernate.hql.QueryTranslator.getResultColumnOrRow(QueryTranslator.java:1005)
at net.sf.hibernate.loader.Loader.getRowFromResultSet(Loader.java:243)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:285)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:138)
at net.sf.hibernate.loader.Loader.doList(Loader.java:1063)
at net.sf.hibernate.loader.Loader.list(Loader.java:1054)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:854)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1554)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:49)
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)

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

The generated SQL (show_sql=true):

Hibernate: select avg(mark0_.score*rating1_.ratingQuality) as x0_0_, avg(rating1_.ratingQuality) as x1_0_ from s_mark mark0_ inner join s_rating rating1_ on mark0_.fk_rating=rating1_.id inner join s_product product2_ on rating1_.fk_product=product2_.id where (product2_.id=? )and(rating1_.approved=1 )and(rating1_.creationDate>=? )and(rating1_.creationDate<? ) group by mark0_.id

Debug level Hibernate log excerpt:

Hibernate: select avg(mark0_.score*rating1_.ratingQuality) as x0_0_, avg(rating1_.ratingQuality) as x1_0_ from s_mark mark0_ inner join s_rating rating1_ on mark0_.fk_rating=rating1_.id inner join s_product product2_ on rating1_.fk_product=product2_.id where (product2_.id=? )and(rating1_.approved=1 )and(rating1_.creationDate>=? )and(rating1_.creationDate<? ) group by mark0_.id
[chooss] DEBUG [http-8080-Processor25] BatcherImpl.getPreparedStatement(253) | preparing statement
[chooss] DEBUG [http-8080-Processor25] NullableType.nullSafeSet(46) | binding '3' to parameter: 1
[chooss] DEBUG [http-8080-Processor25] NullableType.nullSafeSet(46) | binding '2005-04-21 13:19:09' to parameter: 2
[chooss] DEBUG [http-8080-Processor25] NullableType.nullSafeSet(46) | binding '2005-05-21 13:19:09' to parameter: 3
[chooss] DEBUG [http-8080-Processor25] Loader.doQuery(281) | processing result set
[chooss] DEBUG [http-8080-Processor25] Loader.getRow(484) | result row:
[chooss] DEBUG [http-8080-Processor25] NullableType.nullSafeGet(68) | returning '1.0' as column: x0_0_
[chooss] DEBUG [http-8080-Processor25] NullableType.nullSafeGet(68) | returning '1' as column: x1_0_
[chooss] DEBUG [http-8080-Processor25] JDBCExceptionReporter.logExceptions(49) | SQL Exception
java.sql.SQLException: Column 'x2_0_' not found.
at com.mysql.jdbc.ResultSet.findColumn(ResultSet.java:2316)
at com.mysql.jdbc.ResultSet.getFloat(ResultSet.java:1234)
at org.apache.commons.dbcp.DelegatingResultSet.getFloat(DelegatingResultSet.java:242)
at net.sf.hibernate.type.FloatType.get(FloatType.java:16)
at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:62)
at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:53)
at net.sf.hibernate.hql.QueryTranslator.getResultColumnOrRow(QueryTranslator.java:1005)
at net.sf.hibernate.loader.Loader.getRowFromResultSet(Loader.java:243)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:285)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:138)
at net.sf.hibernate.loader.Loader.doList(Loader.java:1063)
at net.sf.hibernate.loader.Loader.list(Loader.java:1054)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:854)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1554)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:49)

Thanks a lot for your help.

Any idea? Could that be a bug?

No.

In future, search the forums before posting, this has been answered VERY MANY times!

(If you want to use expressions in the select clause, get HB3.)

Thanks a lot for the nice reply, Gavin :) Unfortunately phpBB's search is not really efficient, especially in a forum where so many posts use the word 'select', 'operation' or 'expression'.

But thanks again for your time.