The hibernate generate the alias of field. In Having clause, it use the field name, so the database will say that there is no this field in database.

warning : SQL Error: 1054, SQLState: S0022
2004-1-2 8:37:20 net.sf.hibernate.util.JDBCExceptionReporter logExceptions
Hibernate: select forum0_.id as id0_, category1_.id as id1_, message3_.id as id2_, forum0_.name as name0_, forum0_.priority as priority0_, category1_.name as name1_, category1_.priority as priority1_, category1_.description as descript4_1_, category1_.moderator as moderator1_, category1_.forum as forum1_, message3_.toc as toc2_, message3_.text as text2_, message3_.poster as poster2_, message3_.topic as topic2_, message3_.parent as parent2_, forum0_.id as x0_0_, category1_.id as x1_0_, count(distinct topic2_.id) as x2_0_, count(distinct message3_.id) as x3_0_, message3_.id as x4_0_ from Forum forum0_, Category category1_, Topic topic2_, Message message3_ where (message3_.topic=topic2_.id )and(topic2_.category=category1_.id )and(category1_.forum=forum0_.id ) group by topic2_.category , category1_.forum having (message3_.toc=max(message3_.toc)) order by forum0_.priority desc , category1_.priority desc , topic2_.toc desc

fatal: Column not found, message from server: "Unknown column 'message3_.toc' in 'having clause'"
2004-1-2 8:37:20 net.sf.hibernate.JDBCException <init>

fatal: Could not execute query
java.sql.SQLException: Column not found, message from server: "Unknown column 'message3_.toc' in 'having clause'"

For the error message I write in the description, (***message3_.toc as toc2_,******having (message3_.toc=max(message3_.toc))
should be (***message3_.toc******having (message3_.toc=max(message3_.toc))

NOTICE: (***message3_.toc as toc2_******having(toc2_=max(toc2_))) doesn't work

It means if some attribute in the having clause, it should not generate the alias for it. Or it will fail at least against MySQL database

Gavin King [ 06/Jan/04 12:41 AM ] [ Permlink ]
This looks like something for the user forum. Not a bug in Hibernate.



[/b]

Query q=s.createQuery("select new com.juke.bbs.model.ForumInfo(f,c,count(distinct t),count(distinct m),m) from Forum as f,Category as c,Topic as t,Message as m where m.topic=t and t.category=c and c.forum=f group by t.category,c.forum having m.toc = max(m.toc) order by f.priority desc,c.priority desc,t.toc desc");

I actually think this is a bug, net.sf.hibernate.hql.HavingParser looks very simple to me. I will try to fix this in the next days, if nobody is faster then me :)

Well, actually hibernate generates the alias in the having clause just as in the WHERE clause. So this seems to be a MySQL related problem. What would be a SQL that works with MySQL?

select forum0_.id as id0_, category1_.id as id1_, message3_.id as id2_, forum0_.name as name0_, forum0_.priority as priority0_, category1_.name as name1_, category1_.priority as priority1_, category1_.description as descript4_1_, category1_.moderator as moderator1_, category1_.forum as forum1_, message3_.toc as toc2_, message3_.text as text2_, message3_.poster as poster2_, message3_.topic, message3_.parent as parent2_, forum0_.id as x0_0_, category1_.id as x1_0_, count(distinct topic2_.id) as x2_0_, count(distinct message3_.id) as x3_0_, message3_.id as x4_0_ from Forum forum0_, Category category1_, Topic topic2_, Message message3_ where (message3_.topic=topic2_.id )and(topic2_.category=category1_.id )and(category1_.forum=forum0_.id ) group by topic2_.category , category1_.forum having (message3_.toc=max(message3_.toc)) order by forum0_.priority desc , category1_.priority desc , topic2_.toc desc


change the message3_.toc as toc2_, to message3_.toc, it means if there is the having clause about some attribute of the object, this attribute shouldn't generate an alias for it.

in MySQL, just the table alias can be referred in other clause( where, having) than select statment.

we can only use the field alias in the select clause in MySQL.

Well, thats a MySQL problem, Hibernate surely will not be changed to work around that.