Hi guys,
I have been running the HQL query below for ages and it was working fine until I upgraded to v3.6.6. The problem is the SQL being generated for the outer join. The correct SQL should only outer join the InvoiceGroup table. However, v3.6.6 generated a SQL that not only outer join the InvoiceGroup table but also inner join it again. I have attached both the HQL and the generated SQL.
Am I not constructing the HQL correctly or is this a possible bug that was introduced in later version?
Thanks in advance for any insights,
Ben
Code:
select ro.customer,
rp.invoiceGroup,
ro.reportProgram
from ReportOrder ro
join ro.reports r
join ro.reportProgram rp
left join rp.invoiceGroup
where r.isLatest = true
and r.reportStatusCode <> :reportStatusCode
and ro.pricingDate >= :startingPricingDate
and ro.pricingDate < :endingPricingDate
and (not (r.sendToPrinter = false and ro.chargeType = :noChargeType))
group by ro.customer, ro.reportProgram
order by ro.customer, rp.invoiceGroup, ro.reportProgram
Generated SQL:
Code:
select
customer4_.ID as ID81_0_,
invoicegro5_.ID as ID18_1_,
reportprog6_.ID as ID14_2_,
customer4_.Version as Version81_0_,
customer4_.Name as Name81_0_,
customer4_.Parent_ID as Parent4_81_0_,
customer4_.IsActive as IsActive81_0_,
customer4_.DealerID as DealerID81_0_,
customer4_.AE as AE81_0_,
customer4_.CreatedDate as CreatedD8_81_0_,
customer4_.CreatedBy as CreatedBy81_0_,
customer4_.UpdatedDate as Updated10_81_0_,
customer4_.UpdatedBy as UpdatedBy81_0_,
invoicegro5_.Version as Version18_1_,
invoicegro5_.GroupCode as GroupCode18_1_,
invoicegro5_.GroupName as GroupName18_1_,
invoicegro5_.InvoiceByPackingList as InvoiceB5_18_1_,
invoicegro5_.CreatedDate as CreatedD6_18_1_,
invoicegro5_.CreatedBy as CreatedBy18_1_,
invoicegro5_.UpdatedDate as UpdatedD8_18_1_,
invoicegro5_.UpdatedBy as UpdatedBy18_1_,
reportprog6_.Version as Version14_2_,
reportprog6_.Customer_ID as Customer3_14_2_,
reportprog6_.Customer_ProductPrefix_ID as Customer4_14_2_,
reportprog6_.Seller_ID as Seller5_14_2_,
reportprog6_.SellerCode as SellerCode14_2_,
reportprog6_.Description as Descript7_14_2_,
reportprog6_.CanSetExpire as CanSetEx8_14_2_,
reportprog6_.IsTotalWeightRequired as IsTotalW9_14_2_,
reportprog6_.IsSKURequired as IsSKURe10_14_2_,
reportprog6_.UseLabDataFromScan as UseLabD11_14_2_,
reportprog6_.UseProductIDInFilename as UseProd12_14_2_,
reportprog6_.UseCustomerDefaultShipAddress as UseCust13_14_2_,
reportprog6_.DefaultShipAddress_ID as Default14_14_2_,
reportprog6_.DoNotCheckForIncompleteLabData as DoNotCh15_14_2_,
reportprog6_.FTPProfile_ID as FTPProfile16_14_2_,
reportprog6_.ReportBarColor_ID as ReportB17_14_2_,
reportprog6_.InvoiceGroup_ID as Invoice18_14_2_,
reportprog6_.InvoiceByPackingList as Invoice19_14_2_,
reportprog6_.CreatedDate as Created20_14_2_,
reportprog6_.CreatedBy as CreatedBy14_2_,
reportprog6_.UpdatedDate as Updated22_14_2_,
reportprog6_.UpdatedBy as UpdatedBy14_2_
from ReportOrder reportorde0_
inner join Report reports1_
on reportorde0_.ID = reports1_.ReportOrder_ID
inner join ReportProgram reportprog2_
on reportorde0_.ReportProgram_ID = reportprog2_.ID
left outer join InvoiceGroup invoicegro3_
on reportprog2_.InvoiceGroup_ID = invoicegro3_.ID
inner join InvoiceGroup invoicegro5_
on reportprog2_.InvoiceGroup_ID = invoicegro5_.ID
inner join core.customer customer4_
on reportorde0_.Customer_ID = customer4_.ID
inner join ReportProgram reportprog6_
on reportorde0_.ReportProgram_ID = reportprog6_.ID
where reports1_.IsLatest = 1
and reports1_.ReportStatusCode <> ?
and reportorde0_.PricingDate >= ?
and reportorde0_.PricingDate < ?
and (reports1_.SendToPrinter <> 0 or reportorde0_.ChargeTypeCode <> ?)
group by reportorde0_.Customer_ID, reportorde0_.ReportProgram_ID
order by reportorde0_.Customer_ID, reportprog2_.InvoiceGroup_ID, reportorde0_.ReportProgram_ID
Login
FAQ
Search
