Hibernate version: 3.2.2

Hi!
I've been using Spring Framework 1.2 and Hibernate 2.1 for a long time without problems.

I'm now trying to upgrade to Spring 2.0 and Hibernate 3.2 but have run into some problems.

I'm using the PostgreSQL 8.2 server.

HQL:
select new no.gep.mediaport.util.statistics.StatsDataSales(si.sale.time, si.sale.clientLocation.country, si.sale.clientLocation.city, si.sale.currency, si.publication, si.product, si.sale.payMethod, si.amount) from no.gep.mediaport.bus.SaleItem as si where si.sale.time between :from and :to and si.sale.payMethod != :bar

The HQL is generating the following SQL:
select sale1_.sale_time as col_0_0_, sale1_.country_code as col_1_0_, sale1_.cl_city as col_2_0_, sale1_.currency_code as col_3_0_, saleitem0_.pub_id as col_4_0_, saleitem0_.product_id as col_5_0_, sale1_.sale_pay_method as col_6_0_, saleitem0_.saleitem_amount as col_7_0_ from saleitem saleitem0_, sale sale1_ inner join country country3_ on sale1_.country_code=country3_.country_code inner join currency currency6_ on sale1_.currency_code=currency6_.currency_code inner join publication publicatio7_ on saleitem0_.pub_id=publicatio7_.id inner join product product8_ on saleitem0_.product_id=product8_.product_id where saleitem0_.sale_id=sale1_.sale_id and (sale1_.sale_time between ? and ?) and sale1_.sale_pay_method<>? order by sale1_.sale_time

I'm getting this Exception:
org.postgresql.util.PSQLException: ERROR: invalid reference to FROM-clause entry for table "saleitem0_"
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1512)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1297)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:437)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:353)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:257)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1778)
at org.hibernate.loader.Loader.doQuery(Loader.java:662)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2211)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2095)
at org.hibernate.loader.Loader.list(Loader.java:2090)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:375)
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 org.springframework.orm.hibernate3.HibernateTemplate$30.doInHibernate(HibernateTemplate.java:870)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:367)
at org.springframework.orm.hibernate3.HibernateTemplate.findByNamedParam(HibernateTemplate.java:861)
at no.gep.mediaport.dao.hibernate.MiscDAOImpl.getStatisticsDataSales(MiscDAOImpl.java:53)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:281)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:187)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:154)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:107)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:176)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:210)
at $Proxy273.getStatisticsDataSales(Unknown Source)
at no.gep.mediaport.util.statistics.StatisticsUtil.getStatisticsSales(StatisticsUtil.java:45)
at no.gep.mediaport.util.statistics.StatisticsUtil.getStatistics(StatisticsUtil.java:34)
at no.gep.mediaport.web.provider.StatsController.onSubmit(StatsController.java:85)
at org.springframework.web.servlet.mvc.SimpleFormController.processFormSubmission(SimpleFormController.java:267)
at org.springframework.web.servlet.mvc.AbstractFormController.handleRequestInternal(AbstractFormController.java:250)
at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)
at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:45)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:820)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:755)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:396)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:360)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:173)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:77)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at no.gep.mediaport.filter.CommonFilter.doFilterInternal(CommonFilter.java:172)
at no.gep.mediaport.filter.MediaPortFilter.doFilter(MediaPortFilter.java:35)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at no.gep.mediaport.filter.LocaleFilter.doFilterInternal(LocaleFilter.java:136)
at no.gep.mediaport.filter.MediaPortFilter.doFilter(MediaPortFilter.java:35)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at no.gep.mediaport.filter.SecurityFilter.doFilterInternal(SecurityFilter.java:274)
at no.gep.mediaport.filter.MediaPortFilter.doFilter(MediaPortFilter.java:35)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at no.gep.mediaport.filter.CompatFilter.doFilterInternal(CompatFilter.java:64)
at no.gep.mediaport.filter.MediaPortFilter.doFilter(MediaPortFilter.java:35)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:524)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.valves.FastCommonAccessLogValve.invoke(FastCommonAccessLogValve.java:495)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Thread.java:595)

I've tried to copy/paste the query into PostgresSQL and I get the same error message:
select
sale1_.sale_time as col_0_0_,
sale1_.country_code as col_1_0_,
sale1_.cl_city as col_2_0_,
sale1_.currency_code as col_3_0_,
saleitem0_.pub_id as col_4_0_,
saleitem0_.product_id as col_5_0_,
sale1_.sale_pay_method as col_6_0_,
saleitem0_.saleitem_amount as col_7_0_
from
saleitem saleitem0_,
sale sale1_
inner join country country3_
on sale1_.country_code=country3_.country_code
inner join currency currency6_
on sale1_.currency_code=currency6_.currency_code
inner join publication publicatio7_
on saleitem0_.pub_id=publicatio7_.id
inner join product product8_
on saleitem0_.product_id=product8_.product_id
where
saleitem0_.sale_id=sale1_.sale_id and
(sale1_.sale_time between '2007-02-01' and '2007-03-01') and
sale1_.sale_pay_method<>3
order by
sale1_.sale_time;
ERROR: invalid reference to FROM-clause entry for table "saleitem0_"
HINT: There is an entry for table "saleitem0_", but it cannot be referenced from this part of the query.

The problem goes away if I move the saleitem joining like this:
select
sale1_.sale_time as col_0_0_,
sale1_.country_code as col_1_0_,
sale1_.cl_city as col_2_0_,
sale1_.currency_code as col_3_0_,
saleitem0_.pub_id as col_4_0_,
saleitem0_.product_id as col_5_0_,
sale1_.sale_pay_method as col_6_0_,
saleitem0_.saleitem_amount as col_7_0_
from
saleitem saleitem0_
inner join publication publicatio7_
on saleitem0_.pub_id=publicatio7_.id
inner join product product8_
on saleitem0_.product_id=product8_.product_id,
sale sale1_
inner join country country3_
on sale1_.country_code=country3_.country_code
inner join currency currency6_
on sale1_.currency_code=currency6_.currency_code
where
saleitem0_.sale_id=sale1_.sale_id and
(sale1_.sale_time between '2007-02-01' and '2007-03-01') and
sale1_.sale_pay_method<>3
order by
sale1_.sale_time;
col_0_0_ | col_1_0_ | col_2_0_ | col_3_0_ | col_4_0_ | col_5_0_ | col_6_0_ | col_7_0_
-------------------------+----------+----------+----------+----------+----------+----------+----------
2007-02-03 18:18:12.194 | NO | OSLO | NOK | 214 | 1 | 0 | 15
2007-02-03 18:18:12.194 | NO | OSLO | NOK | 241 | 1 | 0 | 12

Is this a bug in Hibernate or do I do something wrong?

If you need more information I'll try to provide it.

Thanks.

I've compared the SQL generated from the HQL in the previos version (Hiberante 2.1) and this one showed in my first post.

With the same HQL I get this SQL with Hibernate 2.1:
select
country2_.country_code as country_1_0_,
currency3_.currency_code as currency1_1_,
publicatio4_.id as id2_,
product5_.product_id as product_id3_,
country2_.country_name as country_2_0_,
country2_.country_vat as country_3_0_,
country2_.country_prefix as country_4_0_,
country2_.language_code as language5_0_,
currency3_.enabled as enabled1_,
currency3_.min_px_amount as min_px_a3_1_,
currency3_.max_px_amount as max_px_a4_1_,
currency3_.min_cpa_amount as min_cpa_5_1_,
currency3_.max_cpa_amount as max_cpa_6_1_,
currency3_.min_cc_amount as min_cc_a7_1_,
currency3_.max_cc_amount as max_cc_a8_1_,
currency3_.lowest_mon_unit as lowest_m9_1_,
publicatio4_.pubid as pubid2_,
publicatio4_.publication_name as publicat3_2_,
publicatio4_.publication_ftp_time as publicat4_2_,
publicatio4_.publication_min_file_size as publicat5_2_,
publicatio4_.publication_sort_order as publicat6_2_,
publicatio4_.publication_enabled as publicat7_2_,
publicatio4_.publication_view_on_frontpage as publicat8_2_,
publicatio4_.parent_id as parent_id2_,
publicatio4_.provider as provider2_,
publicatio4_.publication_type_id as publica11_2_,
product5_.product_name as product_2_3_,
product5_.product_message_key as product_3_3_,
product5_.product_period as product_4_3_,
product5_.product_access_period as product_5_3_,
product5_.product_type_id as product_6_3_,
sale1_.sale_time as x0_0_,
country2_.country_code as x1_0_,
sale1_.cl_city as x2_0_,
currency3_.currency_code as x3_0_,
publicatio4_.id as x4_0_,
product5_.product_id as x5_0_,
sale1_.sale_pay_method as x6_0_,
saleitem0_.saleitem_amount as x7_0_
from
saleitem saleitem0_,
sale sale1_,
country country2_,
currency currency3_,
publication publicatio4_,
product product5_
where
saleitem0_.sale_id=sale1_.sale_id and
saleitem0_.sale_id=sale1_.sale_id and
sale1_.country_code=country2_.country_code and
saleitem0_.sale_id=sale1_.sale_id and
sale1_.currency_code=currency3_.currency_code and
saleitem0_.pub_id=publicatio4_.id and
saleitem0_.product_id=product5_.product_id and
((sale1_.sale_time between ? and ? and saleitem0_.sale_id=sale1_.sale_id)
and(sale1_.sale_pay_method!=? and saleitem0_.sale_id=sale1_.sale_id))
order by
sale1_.sale_time

Any suggestions anyone?