Hi,
We have recently upgraded our application from 3.0.5 to 3.5.5-FINAL and have found an error with the SQL being generated.
We have a simple class with two subclasses.
On the supertype is a 0..1 association to an entity holding error status (Most of the time this will be empty) and used to filter out those entities who have an error and are to be ignored.
The HQL query used is this:
Code:
select distinct billableParty from com.wcg.wsf.billing.BillableParty billableParty
left join billableParty.processRunnableStatus
where billableParty.lineOfBusiness.id = :lineOfBusinessId
and (billableParty.processRunnableStatus is null OR billableParty.processRunnableStatus.ignored = 0)
With 3.5.5 it is generating the following SQL for this query:
Code:
select distinct billablepa0_.ID as ID237_,
billablepa0_.VERSION as VERSION237_,
billablepa0_.LINE_OF_BUSINESS_FK as LINE3_237_,
billablepa0_.PROCESS_RUNNABLE_STATUS_ as PROCESS4_237_ ,
case when billablepa0_1_.ID is not null then 1 when billablepa0_2_.ID is not null then 2 when billablepa0_.ID is not null then 0 end as clazz_
from BILLABLE_PARTY billablepa0_, DLR_BILLABLE_PARTY billablepa0_1_, SUP_BILLABLE_PARTY billablepa0_2_, PROC_RUN_STATUS processrun1_, PROC_RUN_STATUS processrun2_
where billablepa0_.ID=billablepa0_1_.ID(+)
and billablepa0_.ID=billablepa0_2_.ID(+)
and billablepa0_.PROCESS_RUNNABLE_STATUS_=processrun1_.ID(+)
and billablepa0_.PROCESS_RUNNABLE_STATUS_=processrun2_.ID
and billablepa0_.LINE_OF_BUSINESS_FK=2006285483720750007
and (billablepa0_.PROCESS_RUNNABLE_STATUS_ is null or processrun2_.IGNORED=0)
This is incorrectly returning no results.
Going back to a 3.0.5 version of our app and running on the same database, the following SQL used to be generated:
Code:
select distinct billablepa0_.ID as ID, billablepa0_.version as version222_, billablepa0_.LINE_OF_BUSINESS_FK as LINE3_222_, billablepa0_.PROCESS_RUNNABLE_STATUS_ as PROCESS4_222_,
case when billablepa0_1_.ID is not null then 1 when billablepa0_2_.ID is not null then 2 when billablepa0_.ID is not null then 0 end as clazz_
from BILLABLE_PARTY billablepa0_
left outer join DLR_BILLABLE_PARTY billablepa0_1_ on billablepa0_.ID=billablepa0_1_.ID
left outer join SUP_BILLABLE_PARTY billablepa0_2_ on billablepa0_.ID=billablepa0_2_.ID
left outer join PROC_RUN_STATUS processrun1_ on billablepa0_.PROCESS_RUNNABLE_STATUS_=processrun1_.ID
where billablepa0_.LINE_OF_BUSINESS_FK=2006285483720750007
and (billablepa0_.PROCESS_RUNNABLE_STATUS_ is null or processrun1_.IGNORED=0)
This returned the results as expected.
Examining the 3.5.5 query closer I noticed that there is a missing (+) that should be on the second "and billablepa0_.PROCESS_RUNNABLE_STATUS_=processrun2_.ID
"
so the correct query SHOULD actually be:
Code:
select distinct billablepa0_.ID as ID237_,
billablepa0_.VERSION as VERSION237_,
billablepa0_.LINE_OF_BUSINESS_FK as LINE3_237_,
billablepa0_.PROCESS_RUNNABLE_STATUS_ as PROCESS4_237_ ,
case when billablepa0_1_.ID is not null then 1 when billablepa0_2_.ID is not null then 2 when billablepa0_.ID is not null then 0 end as clazz_
from BILLABLE_PARTY billablepa0_, DLR_BILLABLE_PARTY billablepa0_1_, SUP_BILLABLE_PARTY billablepa0_2_, PROC_RUN_STATUS processrun1_, PROC_RUN_STATUS processrun2_
where billablepa0_.ID=billablepa0_1_.ID(+)
and billablepa0_.ID=billablepa0_2_.ID(+)
and billablepa0_.PROCESS_RUNNABLE_STATUS_=processrun1_.ID(+)
and billablepa0_.PROCESS_RUNNABLE_STATUS_=processrun2_.ID(+)
and billablepa0_.LINE_OF_BUSINESS_FK=2006285483720750007
and (billablepa0_.PROCESS_RUNNABLE_STATUS_ is null or processrun2_.IGNORED=0)
Using the SQL directly returns the result I expect.
But this means that the latest Hibernate is not generating the correct SQL.
Anyone else encountered this error?
Is there an easy fix? We have a large application and manually finding and changing all queries will be a huge task and will likely question why we upgraded.
Thanks for ant help you can give me.
Login
FAQ
Search
