NH-3893 - HQL parse error of a query with 'left' or 'right' function

[nhibernate-bot]

bimcio created an issue — 1st August 2016, 8:28:00:

The following code raises exception when trying to parse HQL with 'left' (or 'right') function used in the query. It worked on 3.3.3GA with the classic query translator.

Test code

        static void Main(string[] args)
        {
            try
            {
                var configuration = new Configuration();
                configuration.Configure();
                ISessionFactory sessionFactory = configuration.BuildSessionFactory();
                using (ISession session = sessionFactory.OpenSession())
                {
                    var query1 = session.CreateQuery("from Parent parent where concat(name, 'a')='aaa'");
                    Console.Error.WriteLine("Query1 parsed.");
                    query1.List<Parent>();
                    var query2 = session.CreateQuery("from Parent parent where right(name, 2)='aaa'");
                    Console.Error.WriteLine("Query2 parsed."); // This line is never reached
                    query2.List<Parent>();
                }
            }
            catch (Exception e)
            {
                Console.Error.WriteLine(e.ToString());
            }
            Console.Error.WriteLine("Press enter...");
            Console.ReadLine();
        }

Mapping

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="HQLFunctions" assembly="HQLFunctions">
  <class name="HQLFunctions.Parent" table="Parent">
    <id name="Id" type="Int32" column="Id"/>
    <property name="Name" not-null="true" length="255"/>
  </class>
</hibernate-mapping>

Model

namespace HQLFunctions
{
    public class Parent
    {
        private int id;
        private string name;

        public virtual int Id
        {
            get { return id; }
            set { id = value; }
        }

        public virtual string Name
        {
            get { return name; }
            set { name = value; }
        }
    }
}

Config

<?xml version="1.0" encoding="utf-8" ?>
<session-factory xmlns="urn:nhibernate-configuration-2.2">
  <property name="dialect">NHibernate.Dialect.MsSql2008Dialect</property>
  <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
  <property name="show_sql">false</property>
  <property name="connection.connection_string">Data Source=localhost\sqlexpress;Initial Catalog=testing;Integrated Security=True</property>
   <mapping assembly="HQLfunctions" />
</session-factory>

---

bimcio added a comment — 1st August 2016, 8:31:12:

The exception is:

Query1 parsed.
NHibernate.Hql.Ast.ANTLR.QuerySyntaxException: A recognition error occurred. near line 1, column 25
   bei NHibernate.Hql.Ast.ANTLR.ErrorCounter.ThrowQueryException()
   bei NHibernate.Hql.Ast.ANTLR.HqlParseEngine.Parse()
   bei NHibernate.Hql.StringQueryExpression.Translate(ISessionFactoryImplementor factory, Boolean filter)
   bei NHibernate.Hql.Ast.ANTLR.ASTQueryTranslatorFactory.CreateQueryTranslators(IQueryExpression queryExpression, String collectionRole, Boolean shallow, IDictionary`2 filters, ISessionFactoryImplementor factory)
   bei NHibernate.Engine.Query.QueryExpressionPlan.CreateTranslators(IQueryExpression queryExpression, String collectionRole, Boolean shallow, IDictionary`2 enabledFilters, ISessionFactoryImplementor factory)
   bei NHibernate.Engine.Query.QueryExpressionPlan..ctor(IQueryExpression queryExpression, Boolean shallow, IDictionary`2 enabledFilters, ISessionFactoryImplementor factory)
   bei NHibernate.Engine.Query.QueryPlanCache.GetHQLQueryPlan(IQueryExpression queryExpression, Boolean shallow, IDictionary`2 enabledFilters)
   bei NHibernate.Impl.AbstractSessionImpl.GetHQLQueryPlan(IQueryExpression queryExpression, Boolean shallow)
   bei NHibernate.Impl.AbstractSessionImpl.CreateQuery(String queryString)

---

Oskar Berggren added a comment — 1st August 2016, 8:56:40:

I wonder if left/right might be confused with LEFT/RIGHT OUTER JOIN perhaps.

---

bimcio added a comment — 1st August 2016, 9:02:30:

Seems so. When I register 'rright' function in my inherited dialect and use it in the query then query is parsed fine.

---

Oskar Berggren added a comment — 12th November 2016, 15:30:54:

I wonder if the same problem is present in Hibernate or if they've fixed it. It appears the left/right functions aren't listed in the HQL documentation.

---

Oskar Berggren added a comment — 12th November 2016, 15:48:59:

As noted workaround is to register the function under a different name. Or use substring(). Not letting this prevent release of 4.1.

---

Frédéric Delaporte added a comment — 31st July 2017, 10:59:58:

NHibernate.Test.Legacy.SQLFunctionsTest.DialectSQLFunctions demonstrates the issue, but only for Oracle because this test has an "Oracle only" part which tries using left and right functions.

---

Frédéric Delaporte added a comment — 2nd August 2017, 11:02:15:

A (for now ignored and) dedicated test case has been committed.