Aggregate projection aliases should not be applied to where-clause

Description

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

Activity

Show:

Steve EbersoleMarch 21, 2011 at 7:05 PM

Bulk closing stale resolved issues

nh hockeyplayerJuly 25, 2007 at 5:57 PM

@OneToMany(cascade = CascadeType.ALL)
@JoinColumn(name = "officer_corporation_fk", insertable = true, updatable = true, nullable = true)
@Collection(child = true, inverse = "corporation")
@PropertyDescriptor(readOnly = false, searchable = true)
@OrderBy("lastName")
public Set<Officer> getOfficers()
{
return officers;
}

It's the orderBy annotation thats taking us out.

nh hockeyplayerJuly 25, 2007 at 5:52 PM

Well it prevents us from operating our web app framework.

We cannot perform simple inheritence

Person--<>-----Officer

Person.JAVA
@PropertyDescriptor(summary = true, index = 4)
@NotNull
public String getLastName()
{
return lastName;
}

If this stuff is shutting down whole frameworks why doesn't someone increase the priority.

?

You may continue by restarting the session.
[ +/- ] Exception: could not initialize a collection: [org.trails.demo.Corporation.officers#1]
org.apache.hivemind.ApplicationRuntimeException
could not initialize a collection: [org.trails.demo.Corporation.officers#1]
component: $HibernateEditPage_41@3c1[DefaultEdit] location: context:/WEB-INF/DefaultEdit.page, line 2, column 63
1 <!DOCTYPE page-specification PUBLIC "-//Apache Software Foundation//Tapestry Specification 4.0//EN" "http://jakarta.apache.org/tapestry/dtd/Tapestry_4_0.dtd">
2 <page-specification class="org.trails.page.HibernateEditPage">
3 <property name="model" persist="session"/>
4 <property name="nextPage"/>
5
6 <property name="delegator"/>
7

[ +/- ] Exception: could not initialize a collection: [org.trails.demo.Corporation.officers#1]
org.hibernate.exception.SQLGrammarException
could not initialize a collection: [org.trails.demo.Corporation.officers#1]
SQL: select officers0_.officer_corporation_fk as officer26_2_, officers0_.id as id2_, officers0_.id as id2_1_, officers0_.accessed as accessed2_1_, officers0_.accountNonExpired as accountN4_2_1_, officers0_.accountNonLocked as accountN5_2_1_, officers0_.created as created2_1_, officers0_.credentialsNonExpired as credenti7_2_1_, officers0_.address as address2_1_, officers0_.cellPhone as cellPhone2_1_, officers0_.city as city2_1_, officers0_.country as country2_1_, officers0_.dayPhone as dayPhone2_1_, officers0_.state as state2_1_, officers0_.website as website2_1_, officers0_.zip as zip2_1_, officers0_.dob as dob2_1_, officers0_.emailAddress as emailAd17_2_1_, officers0_.enabled as enabled2_1_, officers0_.firstName as firstName2_1_, officers0_.lastName as lastName2_1_, officers0_.password as password2_1_, officers0_.photo_id as photo24_2_1_, officers0_.username as username2_1_, officers0_.officer_corporation_fk as officer26_2_1_, uploadable1_.id as id3_0_, uploadable1_.accessed as accessed3_0_, uploadable1_.bytes as bytes3_0_, uploadable1_.contentType as contentT4_3_0_, uploadable1_.created as created3_0_, uploadable1_.description as descript6_3_0_, uploadable1_.fileExtension as fileExte7_3_0_, uploadable1_.fileName as fileName3_0_, uploadable1_.filePath as filePath3_0_, uploadable1_.mediaType as mediaType3_0_, uploadable1_.name as name3_0_, uploadable1_.numBytes as numBytes3_0_ from Person officers0_ left outer join UploadableMedia uploadable1_ on officers0_.photo_id=uploadable1_.id where officers0_.officer_corporation_fk=? order by Person.lastName asc SQLState: 42S22 errorCode: 1054 messages:

  • could not initialize a collection: [org.trails.demo.Corporation.officers#1]

  • Unknown column 'Person.lastName' in 'order clause'

throwableCount: 2 throwables:

  • org.hibernate.exception.SQLGrammarException: could not initialize a collection: [org.trails.demo.Corporation.officers#1]

  • com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown column 'Person.lastName' in 'order clause'

[ +/- ] Exception: Unknown column 'Person.lastName' in 'order clause'
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException
Unknown column 'Person.lastName' in 'order clause'
SQLState: 42S22 errorCode: 1054
Stack Trace:

com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936) com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985) com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631) com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723) com.mysql.jdbc.Connection.execSQL(Connection.java:3256) com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1313) com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1448) org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93) org.hibernate.jdbc.AbstractBatcher.getResultSet(Abstrac

Duplicate

Details

Assignee

Reporter

Affects versions

Priority

Created June 21, 2007 at 1:05 PM
Updated March 21, 2011 at 7:05 PM
Resolved September 23, 2007 at 4:54 PM