"select
    partner.PartnerId,partner.PartnerName,partner_loc.DsppCountry.CountryName,
      partner_loc.DsppCountry.CountryCode,detail.DsppPartnerTaxonomy.Name
from

DsppPartner partner
join partner.SetOfDsppPartnerManagement_1 partner_mgmt
join partner.DsppPartnerLocation partner_loc
join partner_mgmt.DsppPartnerTaxonomy_1.SetOfDsppPartnerTaxonomyDetail detail

where

(upper(partner.PartnerName) like upper('%next%') or
upper(partner_mgmt.PreviousPartnerName) like upper('%next%')) and
(partner_mgmt.MarkedForDeletion <> 1) and
detail.DsppLanguage.LangId = 1
order by partner.PartnerName"






executes properly with the Hiberrnate Version 3.0.0, but does not work with the Hibernate Version 3.1.3

Error that is being thrown up :


   EXCEPTION CLASS   : class org.hibernate.exception.GenericJDBCException
   EXCEPTION MESSAGE : could not execute query using scroll
   EXCEPTION CAUSE   : java.sql.SQLException: The column prefix 'dspppartne0_' does not match with a table name or alias              name used in the query.

NOte : also, the query does not execute with plain list method also (i.e. apart from using scroll method)



Investigation done :

with Hibernate Version 3.0.0, SQL string generated is :

[quote]

select dspppartne0_.partner_id as col_0_0_, dspppartne0_.partner_name as col_1_0_, dsppcountr5_.country_name as col_2_0_, dsppcountr5_.country_code as col_3_0_, dspppartne6_.name as col_4_0_ from dspp_partner dspppartne0_ inner join dspp_partner_management setofdsppp1_ on dspppartne0_.partner_id=setofdsppp1_.partner_id inner join dspp_partner_location dspppartne2_ on dspppartne0_.partner_location_id=dspppartne2_.location_id inner join dspp_partner_taxonomy dspppartne3_ on setofdsppp1_.membership_status=dspppartne3_.taxonomy_id inner join dspp_partner_taxonomy_detail setofdsppp4_ on dspppartne3_.taxonomy_id=setofdsppp4_.taxonomy_id, dspp_country dsppcountr5_, dspp_partner_taxonomy dspppartne6_ where dspppartne2_.country=dsppcountr5_.country_id and setofdsppp4_.taxonomy_id=dspppartne6_.taxonomy_id and (((upper(dspppartne0_.partner_name)like upper('%next%'))or(upper(setofdsppp1_.previous_partner_name)like upper('%next%')))and((setofdsppp1_.marked_for_deletion<>1 ))and(setofdsppp4_.lang_id=1 )) order by  dspppartne0_.partner_name

[/quote]

The above SQL string of Hibernate Version 3.0.0 executes properly in SQL Query Analyzer and gives results,


with Hibernate Version 3.1.3, SQL string generated is :

[quote]

select dspppartne0_.partner_id as col_0_0_, dspppartne0_.partner_name as col_1_0_, dsppcountr5_.country_name as col_2_0_, dsppcountr5_.country_code as col_3_0_, dspppartne7_.name as col_4_0_ from dspp_partner dspppartne0_ inner join dspp_partner_management setofdsppp1_ on dspppartne0_.partner_id=setofdsppp1_.partner_id inner join dspp_partner_taxonomy dspppartne3_ on setofdsppp1_.membership_status=dspppartne3_.taxonomy_id inner join dspp_partner_taxonomy_detail setofdsppp4_ on dspppartne3_.taxonomy_id=setofdsppp4_.taxonomy_id, dspp_partner_taxonomy dspppartne7_ inner join dspp_partner_location dspppartne2_ on dspppartne0_.partner_location_id=dspppartne2_.location_id, dspp_country dsppcountr5_ where dspppartne2_.country=dsppcountr5_.country_id and setofdsppp4_.taxonomy_id=dspppartne7_.taxonomy_id and (upper(dspppartne0_.partner_name) like upper('%next%') or upper(setofdsppp1_.previous_partner_name) like upper('%next%')) and setofdsppp1_.marked_for_deletion<>1 and setofdsppp4_.lang_id=1 order by dspppartne0_.partner_name

[/quote]