Count(Distinct ...) does not work

[rashidim]

below HQL does not work with NH 5.4 but works with NH 5.3.14 :
select l.Name, Count(Distinct l.Member.ID) as MemCount from Loan l group by l.Name

[bahusoid]

Need details. Does it throw exception (than provide exception with full stacktrace) or what?

[bahusoid]

Made a quick test (NHibernate.Test/Hql/Ast/HqlFixture.cs):

[Test(Description = "GH-3215" )]
public void CountDistinctWithAlias()
{
	using var sqlLog = new SqlLogSpy();
	
	var hql = "select l.serialNumber, Count(Distinct l.father.id) as MemCount from Mammal l group by l.serialNumber";
	using(var s = OpenSession())
		s.CreateQuery(hql).List();
}

And everything seems to work OK.

[rashidim]

exception :

A recognition error occurred. near line 1, column 327
at NHibernate.Hql.Ast.ANTLR.HqlParser.aggregateDistinctAll()

stacktrace:
   at NHibernate.Hql.Ast.ANTLR.ErrorCounter.ThrowQueryException()
   at NHibernate.Hql.Ast.ANTLR.HqlParseEngine.Parse()
   at NHibernate.Hql.StringQueryExpression.Translate(ISessionFactoryImplementor factory, Boolean filter)
   at NHibernate.Hql.Ast.ANTLR.ASTQueryTranslatorFactory.CreateQueryTranslators(IQueryExpression queryExpression, String collectionRole, Boolean shallow, IDictionary`2 filters, ISessionFactoryImplementor factory)
   at NHibernate.Engine.Query.QueryPlanCache.GetHQLQueryPlan(IQueryExpression queryExpression, Boolean shallow, IDictionary`2 enabledFilters)
   at NHibernate.Impl.AbstractSessionImpl.GetHQLQueryPlan(IQueryExpression queryExpression, Boolean shallow)
   at NHibernate.Impl.AbstractSessionImpl.CreateQuery(String queryString)
   at Behandish.Common.CPersistable.GetList[T](ISession s, Type type, String fetch, Object param, Boolean cache)

hql:
Select OBJECT_ALIAS.Loan.Type, OBJECT_ALIAS.Loan.Code, OBJECT_ALIAS.Loan.Name, OBJECT_ALIAS.Member.Organ.Code, OBJECT_ALIAS.Member.Organ.Name, Sum(OBJECT_ALIAS.FinalAmount) as FinalAmount, Sum(OBJECT_ALIAS.BenefitAmount) as BenefitAmount, sum(OBJECT_ALIAS.DueAmount) as DueAmount, Count(OBJECT_ALIAS.ID) as RequestCount, Count(Distinct OBJECT_ALIAS.Member.ID) as MemCount From Behandish.Railroad.CRequest OBJECT_ALIAS Where ((OBJECT_ALIAS.Status Between 128 And 8192) And (OBJECT_ALIAS.DocDate Between '1399/04/01' And '1400/03/31')) Group By OBJECT_ALIAS.Member.Organ.Code, OBJECT_ALIAS.Member.Organ.Name, OBJECT_ALIAS.Loan.Code, OBJECT_ALIAS.Loan.Name, OBJECT_ALIAS.Loan.Type Order By OBJECT_ALIAS.Member.Organ.Code, OBJECT_ALIAS.Loan.Code

[bahusoid]

So it looks like some regression from #2502 but I can't reproduce it - test case succeeds.
You need to provide a reproducible test case. You can use NHibernate solution or https://github.com/nhibernate/nhibernate-core-testcase (see test templates in NHSpecificTest/GH0000)

[rashidim]

I use this test template and I can reproduce it. below test works with NH 5.3.14 but does not work with NH 5.4 :

Entitys :
class Organ
{
public virtual int Code { get; set; }
public virtual string Name { get; set; }
}
class Loan
{
public virtual int Type { get; set; }
public virtual int Code { get; set; }
public virtual string Name { get; set; }
}
class Member
{
public virtual Organ Organ { get; set; }
public virtual int Code { get; set; }
public virtual string Name { get; set; }
}
class Request
{
public virtual Guid Id { get; set; }
public virtual Loan Loan { get; set; }
public virtual Member Member { get; set; }
public virtual double Amount { get; set; }
public virtual double FinalAmount { get; set; }
public virtual double BenefitAmount { get; set; }
}

Mapping by code:

		mapper.Class<Organ>(rc =>
		{
			rc.Id(x => x.Code);
			rc.Property(x => x.Name);
		});

		mapper.Class<Loan>(rc =>
		{
			rc.Id(x => x.Code);
			rc.Property(x => x.Type);
			rc.Property(x => x.Name);
		});

		mapper.Class<Member>(rc =>
		{
			rc.Id(x => x.Code);
			rc.Property(x => x.Name);
			rc.ManyToOne(x => x.Organ);
		});

		mapper.Class<Request>(rc =>
		{
			rc.Id(x => x.Id, m => m.Generator(Generators.GuidComb));
			rc.Property(x => x.Amount);
			rc.Property(x => x.FinalAmount);
			rc.Property(x => x.BenefitAmount);
			rc.ManyToOne(x => x.Loan);
			rc.ManyToOne(x => x.Member);
		});

OnSetUp:

			var o1 = new Organ { Name = "org1", Code = 1};
			session.Save(o1);

			var m1 = new Member { Name = "m1", Code = 1000, Organ=o1};
			session.Save(m1);

			var l1 = new Loan { Name = "l1", Code = 56};
			session.Save(l1);

			var r1 = new Request { Amount = 2000 , Loan = l1, Member = m1};
			session.Save(r1);



	public void YourTestName()
	{
		using (var session = OpenSession())
		using (var transaction = session.BeginTransaction())
		{
			var hql = "select r.Loan.Type, r.Loan.Code, r.Loan.Name, r.Member.Organ.Code, r.Member.Organ.Name, Sum(r.FinalAmount) as FinalAmount, Sum(r.BenefitAmount) as BenefitAmount, sum(r.Amount) as Amount, Count(r.Id) as RequestCount, Count(Distinct r.Member.Code) as MemCount from Request r Group By r.Member.Organ.Code, r.Member.Organ.Name, r.Loan.Code, r.Loan.Name, r.Loan.Type";
			var result = session.CreateQuery(hql).List();

			Assert.That(result, Has.Count.EqualTo(1));
			transaction.Commit();
		}
	}

[bahusoid]

Yes it's regression from #2502
And It's name related. Member is a reserved keyword in HQL and it somehow breaks parsing.

[bahusoid]

Fixed by #3217