[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-2681) Aggregate projection aliases should not be applied to where-clause

Aggregate projection aliases should not be applied to where-clause ------------------------------------------------------------------ Key: HHH-2681 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2681 Project: Hibernate3 Issue Type: Bug Affects Versions: 3.2.4.sp1 Reporter: Dmitry Katsubo Priority: Minor Please, have a look at the following expression: Criteria criteria = session.createCriteria(ClauseStatisticsImpl.class); criteria.setProjection( Projections.projectionList() .add(Projections.groupProperty("clause"), "clause") .add(Projections.max("useDate"), "useDate")) .add(Property.forName("useDate").eq("2007-12-12")); criteria.addOrder(Order.desc("useDate")).setMaxResults(20).list(); The problem is that the "eq" expression is applied to groupped property. This is not correct and causes SQL exception. Aliases defined for aggregate projections, should be only allied to "group", "having" and "order" clauses. The mapping is: <class name="ClauseStatisticsImpl" table="stc_clause_statistics"> <cache usage="read-write"/> <composite-id> <key-many-to-one name="clause" column="clauseId" class="ClauseImpl"/> <key-property name="useDate"/> </composite-id> </class> The error is: 12:32.22 [ERROR] org.hibernate.util.JDBCExceptionReporter - Column not found: Y1_ in statement [select top ? this_.clauseId as y0_, max(this_.useDate) as y1_ from stc_clause_statistics this_ where this_.userId=? and y1_=? group by this_.clauseId order by y1_ desc] Exception in thread "AWT-EventQueue-0" org.hibernate.exception.SQLGrammarException: could not execute query at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) at org.hibernate.loader.Loader.doList(Loader.java:2223) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104) at org.hibernate.loader.Loader.list(Loader.java:2099) at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1569) at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283) ... 27 more Caused by: java.sql.SQLException: Column not found: Y1_ in statement [select top ? this_.clauseId as y0_, max(this_.useDate) as y1_ from stc_clause_statistics this_ where this_.userId=? and y1_=? and this_.clauseId in (select this0__.id as y0_ from stc_clause this0__ where this0__.userId in (?, ?) and this0__.languageName=? and this0__.procedureName=? and this0__.phaseName=?) group by this_.clauseId order by y1_ desc] at org.hsqldb.jdbc.jdbcUtil.throwError(Unknown Source) at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source) at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source) at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:505) at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:423) at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:139) at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1547) at org.hibernate.loader.Loader.doQuery(Loader.java:673) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236) at org.hibernate.loader.Loader.doList(Loader.java:2220) ... 34 more