NH-3249 - Cannot perform HQL with "COUNT(DISTINCT Date(s.Date))"

[nhibernate-bot]

Hung Tran created an issue — 24th August 2012, 3:55:30:

1/ NHibernate throws error when perform the HQL below

SELECT COUNT(DISTINCT DATE(s.Date))
FROM Sale s
WHERE (s.Finalized = :p0) AND (s.Date between :p1 and :p2) AND (s.Total.Value > :p3)

Then, the exception i got below

at Antlr.Runtime.RecognitionException..ctor(String message, Exception inner, IIntStream input)
at Antlr.Runtime.RecognitionException..ctor(String message)
at NHibernate.Hql.Ast.ANTLR.HqlParser.selectFrom() in Z:\Working Folder\Opensource\nhibernate\svn-root\nhibernate\src\NHibernate\Hql\Ast\ANTLR\Generated\HqlParser.cs:line 1873
at NHibernate.Hql.Ast.ANTLR.HqlParser.queryRule() in Z:\Working Folder\Opensource\nhibernate\svn-root\nhibernate\src\NHibernate\Hql\Ast\ANTLR\Generated\HqlParser.cs:line 1621
at NHibernate.Hql.Ast.ANTLR.HqlParser.selectStatement() in Z:\Working Folder\Opensource\nhibernate\svn-root\nhibernate\src\NHibernate\Hql\Ast\ANTLR\Generated\HqlParser.cs:line 1237
at NHibernate.Hql.Ast.ANTLR.HqlParser.statement() in Z:\Working Folder\Opensource\nhibernate\svn-root\nhibernate\src\NHibernate\Hql\Ast\ANTLR\Generated\HqlParser.cs:line 440
at NHibernate.Hql.Ast.ANTLR.HqlParseEngine.Parse() in Z:\Working Folder\Opensource\nhibernate\svn-root\nhibernate\src\NHibernate\Hql\Ast\ANTLR\QueryTranslatorImpl.cs:line 470
at NHibernate.Hql.Ast.ANTLR.ASTQueryTranslatorFactory.CreateQueryTranslators(String queryString, String collectionRole, Boolean shallow, IDictionary2 filters, ISessionFactoryImplementor factory) in Z:\Working Folder\Opensource\nhibernate\svn-root\nhibernate\src\NHibernate\Hql\Ast\ANTLR\ASTQueryTranslatorFactory.cs:line 19 at NHibernate.Engine.Query.HQLStringQueryPlan.CreateTranslators(String hql, String collectionRole, Boolean shallow, IDictionary2 enabledFilters, ISessionFactoryImplementor factory) in Z:\Working Folder\Opensource\nhibernate\svn-root\nhibernate\src\NHibernate\Engine\Query\HQLStringQueryPlan.cs:line 24
at NHibernate.Engine.Query.HQLStringQueryPlan..ctor(String hql, String collectionRole, Boolean shallow, IDictionary2 enabledFilters, ISessionFactoryImplementor factory) in Z:\Working Folder\Opensource\nhibernate\svn-root\nhibernate\src\NHibernate\Engine\Query\HQLStringQueryPlan.cs:line 16 at NHibernate.Engine.Query.HQLStringQueryPlan..ctor(String hql, Boolean shallow, IDictionary2 enabledFilters, ISessionFactoryImplementor factory) in Z:\Working Folder\Opensource\nhibernate\svn-root\nhibernate\src\NHibernate\Engine\Query\HQLStringQueryPlan.cs:line 10
at NHibernate.Engine.Query.QueryPlanCache.GetHQLQueryPlan(String queryString, Boolean shallow, IDictionary`2 enabledFilters) in Z:\Working Folder\Opensource\nhibernate\svn-root\nhibernate\src\NHibernate\Engine\Query\QueryPlanCache.cs:line 61
at NHibernate.Impl.AbstractSessionImpl.GetHQLQueryPlan(String query, Boolean shallow) in Z:\Working Folder\Opensource\nhibernate\svn-root\nhibernate\src\NHibernate\Impl\AbstractSessionImpl.cs:line 304
at NHibernate.Impl.AbstractSessionImpl.CreateQuery(String queryString) in Z:\Working Folder\Opensource\nhibernate\svn-root\nhibernate\src\NHibernate\Impl\AbstractSessionImpl.cs:line 283

2/ I have tried the similar SQL with MySQL

SELECT COUNT(DISTINCT DATE(sale0*.DATE)) AS col_0_0*
FROM SALE sale0_
WHERE sale0*.FINALIZED=1 AND (sale0*.DATE BETWEEN '2012-07-1' AND '2012-07-31 23:59:59')
AND sale0*.TOTAL*VALUE> 0.0;

=> it works perfect, but HQL cannot generate similar SQL like this.

Framework: .NET 2.0 / 3.5
Data Driver = .NHibernate.Driver.MySqlDataDriver
Dialect = .NHibernate.Dialect.MySQL5Dialect
MySQL = version 5.1.32

---

Oskar Berggren added a comment — 24th August 2012, 7:14:52:

The exception information is incomplete - there should be an exception message also.

Could you please experiment with variations (simplifications) of the HQL query to determine exactly which part is causing the problem?

---

Hung Tran added a comment — 24th August 2012, 8:01:10:

The exception throwed from HqlParser.selectFrom() method at the line 1873 in HqlParser class (NHibernate\Hql\Ast\ANTLR\Generated\HqlParser.cs)

"throw new RecognitionException("FROM expected (non-filter queries must contain a FROM clause)");"
NHibernate\Hql\Ast\ANTLR\Generated\HqlParser.cs:line 1873