Nhibernate criteria query inserts an extra order by expression when using JoinType.LeftOuterJoin and Projections

Description

return Session.CreateCriteria(typeof(FundingCategory), "fc")
.CreateCriteria("FundingPrograms", "fp")
.CreateCriteria("Projects", "p", JoinType.LeftOuterJoin)
.Add(Restrictions.Disjunction()
.Add(Restrictions.Eq("fp.Recipient.Id", recipientId))
.Add(Restrictions.Eq("p.Recipient.Id", recipientId))
)
.SetProjection(Projections.ProjectionList()
.Add(Projections.GroupProperty("fc.Name"), "fcn")
.Add(Projections.Sum("fp.ObligatedAmount"), "fpo")
.Add(Projections.Sum("p.ObligatedAmount"), "po")
)
.AddOrder(Order.Desc("fpo"))
.AddOrder(Order.Desc("po"))
.AddOrder(Order.Asc("fcn"))
.List<object[]>();

produces this SQL with sql2005 dialect.

SELECT this_.Name as y0_,
sum(fp1_.ObligatedAmount) as y1_,
sum(p2_.ObligatedAmount) as y2_
FROM fundingCategories this_
inner join fundingPrograms fp1_
on this_.fundingCategoryId = fp1_.fundingCategoryId
left outer join projects p2_
on fp1_.fundingProgramId = p2_.fundingProgramId
WHERE (fp1_.recipientId = 6 /* @p0 */
or p2_.recipientId = 6 /* @p1 */)
GROUP BY this_.Name
ORDER BY p2_.name asc,
y1_ desc,
y2_ desc,
y0_ asc

The order by is incorrectly adding p2_.name asc. Projects does contain a mapping to its "Name", but I'm calling for FundingCategory's Name. Query fails because there is a field in the order by that doesn't exist in the group by (and I don't want to group by p2_.name).

Environment

None

Assignee

Alex Zaytsev

Reporter

Aaron Palmer

Labels

Components

Fix versions

Affects versions

Priority

Major
Configure