I've read through about everything I can find and can't figure out why it is that I'm getting this error.

I am getting an error that says "unexpected token: with" when executing the following query:

select new
com.solentosystems.nafspronet.beans.DtmlReportItem(user.firstName,
user.lastName, user.emailAddress, user.orgUnit.name,
count(distinct quote.id), count(distinct app.id))
from User user
left join user.applications as app
with app.class =
com.solentosystems.nafspronet.beans.DecreasingTermMortgageLifeApplication
and app.created between :startDate and :endDate
left join user.quotes as quote
with quote.class = com.solentosystems.nafspronet.beans.DecreasingTermMortgageLifeQuote
and quote.quoted between :startDate and :endDate
group by user

Hibernate version:
3.0

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

Hibernate 3.1 gives me a little bit different output. It actually generated a query for me, but the query uses a table alias that isn't actually defined in the query, and I get an error on that. The same exact HQL query above generates the following SQL query in 3.1:

select user0_.firstName as col_0_0_, user0_.lastName as col_1_0_, user0_.emailAddress as col_2_0_, orgunit3_.name as col_3_0_, count(distinct quotes2_.id) as col_4_0_, count(distinct applicatio1_.id) as col_5_0_ from user user0_ left outer join Application applicatio1_ on user0_.id=applicatio1_.user and (case when applicatio1_1_.id is not null then 1 when applicatio1_.id is not null then 0 end=1 and (applicatio1_.created between ? and ?)) left outer join quote quotes2_ on user0_.id=quotes2_.user and (case when quotes2_1_.id is not null then 1 when quotes2_.id is not null then 0 end=1 and (quotes2_.quoted between ? and ?)), orgUnit orgunit3_ where user0_.orgUnitId=orgunit3_.id group by user0_.id

I forgot to post the error it is displaying now:

13:54:42,655 WARN JDBCExceptionReporter:71 - SQL Error: 1109, SQLState: 42S02
13:54:42,655 ERROR JDBCExceptionReporter:72 - Unknown table 'applicatio1_1_' in on clause

I renamed the incorrect table alias in the query, and the query executed fine, so it looks like Hibernate is generating incorrect SQL. Does anyone know of any reason why this would be happening?

Is query executing fine in Hibernate 3.0?

No. In Hibernate 3.0 I got the initial error that I posted, basically saying that the query could not be parsed. Then, in 3.1 the query is parsed but incorrect SQL is generated.

Sorry, but I got a bit more information about the issue. It only happens when the app.class and quote.class clauses are in the HQL query. If I remove those, the query runs fine.

Unfortunately, I need to be able to filter out all but the DecreasingTermMortgageLifeApplication and Quote objects.

I know I could use a filter... and I'm going to right now, but I still am wondering if I should be able to do this directly in HQL.

I can't really use a filter, because I need to get a count on the attribute that I would have filtered. So, does anyone have any idea why my query isn't working?