NH-1761 - Criteria query inserts an extra order by expression when using JoinType.LeftOuterJoin and Projections

[nhibernate-bot]

Aaron Palmer created an issue — 30th April 2009, 9:51:29:

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).

---

Fabio Maulo added a comment — 10th June 2009, 21:55:41:

Aron, please attach mappings and classes if you have it.
Imagine the work we should do before have a failing test and is preferable if we spent the time to fix the problem.
NH2.1.0 is coming very soon.

---

Fabio Maulo added a comment — 10th June 2009, 21:59:13:

btw
Projections.GroupProperty("fc.Name")
is who is auto adding the order

---

Aaron Palmer added a comment — 11th June 2009, 7:22:41:

Here are our maps.

---

Fabio Maulo added a comment — 18th October 2009, 14:32:09:

If I have understood, you don't want this issue fixed.