Hibernate version:
3.2.5.ga, 3.3.1ga
Mapping documents:
The relevant HBM files follow (originally one class per file):
Code:
<hibernate-mapping>
<class name="com.vhm.dal.ContributedItem" table="contributed_items">
<id column="contrib_id" name="id" type="long">
<generator class="native"/>
</id>
<property column="offensive" name="offensive" not-null="true" type="boolean"/>
<property column="visible" name="visible" not-null="true" type="boolean"/>
<many-to-one class="com.vhm.dal.User" column="user_id" name="user" not-null="true"/>
<set inverse="true" name="votes">
<key column="contrib_id" not-null="true"/>
<one-to-many class="com.vhm.dal.Vote"/>
</set>
</class>
<joined-subclass extends="com.vhm.dal.ContributedItem" name="com.vhm.dal.Question" table="questions">
<key column="contrib_id"/>
<property column="question_text" name="text" not-null="true" type="string"/>
<property column="asked" name="asked" not-null="false" type="timestamp"/>
<set inverse="true" name="answers">
<key column="question_id" not-null="true"/>
<one-to-many class="com.vhm.dal.Answer"/>
</set>
<many-to-one class="com.vhm.dal.Event" column="event_id" name="event" not-null="true"/>
</joined-subclass>
<joined-subclass extends="com.vhm.dal.ContributedItem" name="com.vhm.dal.Answer" table="answers">
<key column="contrib_id"/>
<property column="answer_text" name="text" not-null="true" type="string"/>
<many-to-one class="com.vhm.dal.Question" column="question_id" name="question" not-null="true"/>
</joined-subclass>
<joined-subclass extends="com.vhm.dal.ContributedItem" name="com.vhm.dal.Event" table="events">
<key column="contrib_id"/>
<property column="begin_date" name="beginDate" not-null="true" type="timestamp"/>
<property column="end_date" name="endDate" not-null="true" type="timestamp"/>
<property name="title" not-null="true"/>
<set inverse="true" name="questions">
<key column="event_id" not-null="true"/>
<one-to-many class="com.vhm.dal.Question"/>
</set>
<many-to-one class="com.vhm.dal.Show" column="show_id" name="show" not-null="true"/>
</joined-subclass>
<class name="com.vhm.dal.User" table="users">
<id column="user_id" name="id">
<generator class="native"/>
</id>
<property name="name" not-null="true" unique="true"/>
<property name="password" not-null="true"/>
<property name="lockedOut" not-null="true"/>
<set name="roles" table="user_role">
<key column="user_id"/>
<many-to-many class="com.vhm.dal.Role" column="role_id"/>
</set>
</class>
<class name="com.vhm.correlation.dal.CorrGroup" table="corr_group">
<id column="corr_group_id" name="id">
<generator class="native"/>
</id>
<many-to-one column="event_id" name="event" not-null="true"/>
<many-to-one column="quest_id" name="question" not-null="true"/>
<set inverse="true" name="corrQuestions">
<key column="corr_group_id" not-null="true"/>
<one-to-many class="com.vhm.correlation.dal.CorrQuestion"/>
</set>
</class>
<class name="com.vhm.correlation.dal.CorrQuestion" table="corr_quest">
<id column="corr_quest_id" name="id">
<generator class="native"/>
</id>
<property column="corr_type" name="type" not-null="true" type="integer"/>
<property name="score" not-null="true" type="float"/>
<property name="weight" not-null="true" type="float"/>
<property column="per_match" name="perMatch" not-null="true" type="float"/>
<property column="per_this" name="perThis" not-null="true" type="float"/>
<property name="detached" not-null="true" type="boolean"/>
<property name="manual" not-null="true" type="boolean"/>
<many-to-one class="com.vhm.dal.Question" column="quest1_id" name="question1" not-null="true"/>
<many-to-one class="com.vhm.dal.Question" column="quest2_id" name="question2" not-null="false"/>
<many-to-one class="com.vhm.correlation.dal.CorrGroup" column="corr_group_id" name="corrGroup" not-null="true"/>
</class>
</hibernate-mapping>
Code between sessionFactory.openSession() and session.close():I have the following HQL:
Code:
"select new com.vhm.dal.QuestionDAO$QuestionListItem( \n"+
" cquest.question1.id, \n"+
" cast(0 as long), \n" +
" cast(0 as long), \n" +
" cquest.question1.text, \n"+
" quest2.text, \n"+
" cquest.question1.user.name, \n"+
" cquest.question1.asked, \n"+
" (select count(text) from Answer ans where ans.question.id = cquest.question1.id), \n"+
" sum(case when v.user.id = :userId then v.weight else 0 end), \n"+
" sum(case when cquest.question1.user.id = :userId then 1 else 0 end) as myScore )\n"+
" from CorrGroup cgroup \n"+
" inner join cgroup.corrQuestions as cquest \n"+
" left join cquest.question1.votes as v \n"+
" left join cquest.question2 as quest2 \n"+
" where cgroup.event.id = :eventId \n"+
" group by cquest.question1.id, cquest.question1.text, cquest.question1.user.name, \n"+
" cquest.question1.asked, quest2.text \n"+
" order by cquest.question1.id desc"
Full stack trace of any exception that occurs: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.hql.QueryLoader.list(QueryLoader.java:378)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at com.vhm.hibernate_dal.HibernateQuestionDAO.getQuestionListByEvent(HibernateQuestionDAO.java:142)
at com.vhm.domain.QuestionManager.findQuestionsByEventId(QuestionManager.java:54)
at com.vhm.webapp.widget.WidgetServlet.processRequest(WidgetServlet.java:530)
at com.vhm.webapp.widget.WidgetServlet.doGet(WidgetServlet.java:607)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:690)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.vhm.hibernate_dal.HibernateSessionRequestFilter.doFilter(HibernateSessionRequestFilter.java:42)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:230)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:182)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:524)
at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:84)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:157)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:262)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:446)
at java.lang.Thread.run(Thread.java:619)
Caused by: org.postgresql.util.PSQLException: ERROR: invalid reference to FROM-clause entry for table "corrquesti1_"
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1592)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1327)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:350)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:342)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
at org.hibernate.loader.Loader.doQuery(Loader.java:674)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2220)
... 35 more
Name and version of the database you are using:postgresql-8.3 (using postgresql dialect)
The generated SQL:Code:
select corrquesti1_.quest1_id as col_0_0_,
cast(0 as int8) as col_1_0_,
cast(0 as int8) as col_2_0_,
question2_.question_text as col_3_0_,
question4_.question_text as col_4_0_,
user7_.name as col_5_0_,
question2_.asked as col_6_0_,
(select count(answer9_.answer_text)
from answers answer9_
inner join contributed_items answer9_1_
on answer9_.contrib_id=answer9_1_.contrib_id
where answer9_.question_id=corrquesti1_.quest1_id) as col_7_0_,
sum(case when votes3_.user_id=? then votes3_.weight else 0 end) as col_8_0_,
sum(case when question2_1_.user_id=? then 1 else 0 end) as col_9_0_
from corr_group corrgroup0_
inner join corr_quest corrquesti1_
on corrgroup0_.corr_group_id=corrquesti1_.corr_group_id
left outer join questions question2_
on corrquesti1_.quest1_id=question2_.contrib_id
left outer join contributed_items question2_1_
on question2_.contrib_id=question2_1_.contrib_id
left outer join votes votes3_
on question2_.contrib_id=votes3_.contrib_id,
users user7_
left outer join questions question4_
on corrquesti1_.quest2_id=question4_.contrib_id /******* THIS IS THE LINE THAT IS WRONG *********/
left outer join contributed_items question4_1_
on question4_.contrib_id=question4_1_.contrib_id
where question2_1_.user_id=user7_.user_id
and corrgroup0_.event_id=?
group by corrquesti1_.quest1_id ,
question2_.question_text,
user7_.name,
question2_.asked,
question4_.question_text
order by corrquesti1_.quest1_id desc
Debug level Hibernate log excerpt:
3:00:43,267 WARN [JDBCExceptionReporter] SQL Error: 0, SQLState: 42P01
13:00:43,267 ERROR [JDBCExceptionReporter] ERROR: invalid reference to FROM-clause entry for table "corrquesti1_"
Output from pgAdmin when trying to run the above SQL:
ERROR: invalid reference to FROM-clause entry for table "corrquesti1_"
LINE 26: on corrquesti1_.quest2_id=question4_.contrib_id ^
HINT: There is an entry for table "corrquesti1_", but it cannot be referenced from this part of the query.
The question:
So the question is. What am I doing wrong? is this a bug with Hibernate? If so, how can I avoid it? I realize there is alot of code above. I have other queries of similiar complexity that seem fine. This is rather frustrating, since the HQL seems to be fine.
I believe the issue might be the cross join mixed with the other joins. Maybe something with the precedence of the , operator? I am currently doing an SQLQuery with the generated sql and I simply change the cross join into an inner join. But this solution is a hack, I want to just use HQL.
Thank you in advance!
Login
FAQ
Search