Hibernate version:
3.1 (w/ annotations)
Name and version of the database you are using:
MySQL 4.1.10a
I am having issues with a having clause in a query. The query that I am trying to execute looks something like (trimmed for readability):
Code:
select c.claimNumber
from Claim c where
...
group by
c.claimNumber
having
min(c.lineItems.fromDate) = :fromdate and
max(c.lineItems.toDate) = :todate and
sum(c.lineItems.billed) : billed
The resulting query ends up like (again trimmed for readibility):
Code:
select claim0_.claim_number as col_0_0_ from claims claim0_, lineitems lineitems7_, lineitems lineitems8_, lineitems lineitems9_ where
claim0_.claim_number=lineitems7_.claim_number and
claim0_.claim_number=lineitems8_.claim_number and
claim0_.claim_number=lineitems9_.claim_number
group by
claim0_.claim_number
having
min(lineitems7_.from_date)=? and
max(lineitems8_.to_date)=? and
sum(lineitems9_.billed)=?
The problem is that because the query is joining my claims to my line items table 3 different times rather than just re-using the lineitems relation that was already established (lineitems7_). The query actually ends joining the lineitems table to itself 3 times. This completely screws up sums (and counts too I am sure). For example: In one query I am hitting 4 lines. Rather than getting a sum of 140.09 I am seeing a sum of 2241.41 (104.09 * 4 * 4).
What I would like to see is a query more like:
Code:
select claim0_.claim_number as col_0_0_ from claims claim0_, lineitems lineitems7_ where
claim0_.claim_number=lineitems7_.claim_number
group by
claim0_.claim_number
having
min(lineitems7_.from_date)=? and
max(lineitems7_.to_date)=? and
sum(lineitems7_.billed)=?
Is this a known issue (couldn't find anything in JIRA)? Maybe it's expected behavior (I hope not!)? Is there a workaround for this? For now I am probably going to go back to plain ole' SQL, but I would like to find a HQL solution if one exists. HQL is much easier to write and maintain.
Thanks,
Josh
Login
FAQ
Search