I'm trying to use QueryDSL with Hibernate and I've run into a problem that looks like a bug to me. I'm trying to run a query involving three tables/entities:
- WeeklyFinancials holds sales data, with one row per week per terminal
- Equipment holds terminal data
- Location holds location data; each piece of equipment is assigned to a location
I'm trying to write a query to determine year-to-date sales. The query is going to be projected to a YtdSales object, and is meant to return one YtdSales object for each terminal at a specified location. The query fails with:
Code:
org.hibernate.exception.GenericJDBCException: Column 'dbo.terminals.LOCATION_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
So I started investigating. It turns out QueryDSL does not use JPQL constructor expressions, so it generates JPQL that looks like this:
Code:
select equipment, weeklyFinancials.gameCategory, sum(weeklyFinancials.salesAmount), sum(weeklyFinancials.payAmount), sum(weeklyFinancials.commissionAmount)
from WeeklyFinancials weeklyFinancials
join weeklyFinancials.terminal as equipment
where weeklyFinancials.weekEnding between ?1 and ?2 and equipment.location = ?3
group by weeklyFinancials.gameCategory, equipment
...which in turn causes Hibernate to generate SQL that looks like this:
Code:
select equipment1_.id as col_0_0_,
weeklyfina0_.game_category as col_1_0_,
sum(weeklyfina0_.sales_amt) as col_2_0_,
sum(weeklyfina0_.pay_amt) as col_3_0_,
sum(weeklyfina0_.sales_comm) as col_4_0_,
equipment1_.id as id8_,
equipment1_.location_id as location5_8_,
equipment1_.primary_terminal as primary2_8_,
equipment1_.retailer_number as retailer3_8_,
equipment1_.terminal_type_desc as terminal4_8_
from dbo.retailer_acct_weekly weeklyfina0_
inner join dbo.terminals equipment1_ on weeklyfina0_.terminal_id=equipment1_.id
where (weeklyfina0_.end_date between ? and ?) and equipment1_.location_id=?
group by weeklyfina0_.game_category , equipment1_.id
Note all of the equipment1_ entries in the select clause. The database is 100% correct in complaining about this. But here comes the interesting part: if I write the JPQL myself and use a constructor expression, like this:
Code:
select new YtdSales(equipment, weeklyFinancials.gameCategory, sum(weeklyFinancials.salesAmount), sum(weeklyFinancials.payAmount), sum(weeklyFinancials.commissionAmount))
from WeeklyFinancials weeklyFinancials
join weeklyFinancials.terminal as equipment
where weeklyFinancials.weekEnding between ?1 and ?2 and equipment.location = ?3
group by weeklyFinancials.gameCategory, equipment
...then Hibernate generates SQL that looks like this:
Code:
select equipment1_.id as col_0_0_,
weeklyfina0_.game_category as col_1_0_,
sum(weeklyfina0_.sales_amt) as col_2_0_,
sum(weeklyfina0_.pay_amt) as col_3_0_,
sum(weeklyfina0_.sales_comm) as col_4_0_
from dbo.retailer_acct_weekly weeklyfina0_
inner join dbo.terminals equipment1_ on weeklyfina0_.terminal_id=equipment1_.id
where (weeklyfina0_.end_date between ? and ?) and equipment1_.location_id=?
group by weeklyfina0_.game_category , equipment1_.id
...which is completely valid and is what I expected the first JPQL to also do.
My question: is the fact that the first query (which does not use a constructor expression but is otherwise identical to the second query) does not work a bug, or is that the expected behavior?
Login
FAQ
Search