We have recently updated nhibernate from an old version 1.2.0 to
version 2.1.0.4000
An old hql query fails, because nhibernate doesn't translate the where
clause, because it is exceeded by a Group by.
Example HQL failure:
select distinct ka.Oid, ka.Nr, k.ProvenuModtager.EntityOID, min
(k.OprettetDato), ka.Status, sum(k.NettoProvenu), min(k.ValoerDato),
k.LaaneSagNr, ka.Initialer
from KoebsAfregning ka join ka.Koeb k
group by ka.Oid, ka.Nr, k.ProvenuModtager.EntityOID, ka.Status,
k.LaaneSagNr, ka.Initialer
where ka.Aktiv = 1 and k.Salg is null and ka.Nr >= 2
Resulting SQL:
select distinct koebsafreg0_.koebsAfregningOID as col_0_0_,
koebsafreg0_.nr as col_1_0_,
provenumod2_.entityOID as col_2_0_,
min(koeb1_1_.oprettetDato) as col_3_0_,
koebsafreg0_.status as col_4_0_,
sum(koeb1_1_.nettoProvenu) as col_5_0_,
min(koeb1_1_.valoerDato) as col_6_0_,
koeb1_.laaneSagNr as col_7_0_,
koebsafreg0_.initialer as col_8_0_
from koebsAfregninger koebsafreg0_
inner join koeb koeb1_
on koebsafreg0_.koebsAfregningOID =
koeb1_.koebsAfregningOID
inner join transaktioner koeb1_1_
on koeb1_.koebOID = koeb1_1_.transaktionOID,
provenuModtagere provenumod2_
where koeb1_1_.provenuModtagerOID = provenumod2_.provenuModtagerOID
group by koebsafreg0_.koebsAfregningOID,
koebsafreg0_.nr,
provenumod2_.entityOID,
koebsafreg0_.status,
koeb1_.laaneSagNr,
koebsafreg0_.initialer
Where did the where clause go, in the SQL ?
Now i move the group by to the end of the HQL
select distinct ka.Oid, ka.Nr, k.ProvenuModtager.EntityOID, min
(k.OprettetDato), ka.Status, sum(k.NettoProvenu), min(k.ValoerDato),
k.LaaneSagNr, ka.Initialer
from KoebsAfregning ka join ka.Koeb k
where ka.Aktiv = 1 and k.Salg is null and ka.Nr >= 2
group by ka.Oid, ka.Nr, k.ProvenuModtager.EntityOID, ka.Status,
k.LaaneSagNr, ka.Initialer
Resulting SQL is perfect
select distinct koebsafreg0_.koebsAfregningOID as col_0_0_,
koebsafreg0_.nr as col_1_0_,
provenumod2_.entityOID as col_2_0_,
min(koeb1_1_.oprettetDato) as col_3_0_,
koebsafreg0_.status as col_4_0_,
sum(koeb1_1_.nettoProvenu) as col_5_0_,
min(koeb1_1_.valoerDato) as col_6_0_,
koeb1_.laaneSagNr as col_7_0_,
koebsafreg0_.initialer as col_8_0_
from koebsAfregninger koebsafreg0_
inner join koeb koeb1_
on koebsafreg0_.koebsAfregningOID =
koeb1_.koebsAfregningOID
inner join transaktioner koeb1_1_
on koeb1_.koebOID = koeb1_1_.transaktionOID,
provenuModtagere provenumod2_
where koeb1_1_.provenuModtagerOID = provenumod2_.provenuModtagerOID
and koebsafreg0_.aktiv = 1
and (koeb1_.salgOID is null)
and koebsafreg0_.nr >= 2
group by koebsafreg0_.koebsAfregningOID,
koebsafreg0_.nr,
provenumod2_.entityOID,
koebsafreg0_.status,
koeb1_.laaneSagNr,
koebsafreg0_.initialer
Suddenly my query is correct.
If its like SQL and group is to be placed in the end of the HQL an
exception would be cool.
Else perhaps parse it, as it was done in the older versions of
nhibernate, where the order of "Where" and "group by" doesnt matter.
Because this could give potential bugs, wrong queries without any
warnings.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en
-~----------~----~----~----~------~----~------~--~---
