NH-1968 - Locking on queries that return scalar values throws exception: could not locate alias to apply lock mode

[nhibernate-bot]

Johannes Gustafsson created an issue — 16th September 2009, 4:52:36:

Given this mapping:

  <class name="A">
    <id name="Id" type="Int32">
      <generator class="assigned" />
    </id>
  </class>

I want to save a new entity with Id = max(Id) 1:

  using (var session = OpenSession())
  using (var tx = session.BeginTransaction(IsolationLevel.Serializable))
  {
    var maxId = session.CreateQuery("select max(Id) from A a")
      .SetLockMode("a", LockMode.Upgrade)
      .UniqueResult() as int?;
    
    if (!maxId.HasValue)
      maxId = 1;

    session.Save(new A() { Id = maxId.Value </ins> 1, Data = "Test" });
  }

This one fails on the first query with error:

13:33:01,413 WARN ADOExceptionReporter:31 - System.InvalidOperationException: could not locate alias to apply lock mode : a
at NHibernate.Hql.Ast.ANTLR.Loader.QueryLoader.ApplyLocks(SqlString sql, IDictionary`2 lockModes, Dialect dialect) in D:\Dev\Source\NHibernate\trunk\nhibernate\src\NHibernate\Hql\Ast\ANTLR\Loader\QueryLoader.cs:line 81
at NHibernate.Loader.Loader.PreprocessSQL(SqlString sql, QueryParameters parameters, Dialect dialect) in D:\Dev\Source\NHibernate\trunk\nhibernate\src\NHibernate\Loader\Loader.cs:line 204
at NHibernate.Loader.Loader.PrepareQueryCommand(QueryParameters queryParameters, Boolean scroll, ISessionImplementor session) in D:\Dev\Source\NHibernate\trunk\nhibernate\src\NHibernate\Loader\Loader.cs:line 1115
at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in D:\Dev\Source\NHibernate\trunk\nhibernate\src\NHibernate\Loader\Loader.cs:line 399
at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in D:\Dev\Source\NHibernate\trunk\nhibernate\src\NHibernate\Loader\Loader.cs:line 236
at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in D:\Dev\Source\NHibernate\trunk\nhibernate\src\NHibernate\Loader\Loader.cs:line 1644
13:33:01,516 ERROR ADOExceptionReporter:32 - could not locate alias to apply lock mode : a

System.InvalidOperationException: could not locate alias to apply lock mode : a
at NHibernate.Hql.Ast.ANTLR.Loader.QueryLoader.ApplyLocks(SqlString sql, IDictionary2 lockModes, Dialect dialect) in QueryLoader.cs: line 81 at NHibernate.Loader.Loader.PreprocessSQL(SqlString sql, QueryParameters parameters, Dialect dialect) in Loader.cs: line 204 at NHibernate.Loader.Loader.PrepareQueryCommand(QueryParameters queryParameters, Boolean scroll, ISessionImplementor session) in Loader.cs: line 1115 at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in Loader.cs: line 399 at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in Loader.cs: line 236 at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in Loader.cs: line 1644 NHibernate.ADOException: could not execute query < select max(a0*.Id) as col_0_0_ from A a0* > [SQL: select max(a0*.Id) as col_0_0_ from A a0*] at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in Loader.cs: line 1653 at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) in Loader.cs: line 1568 at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet1 querySpaces, IType[] resultTypes) in Loader.cs: line 1562
at NHibernate.Hql.Ast.ANTLR.Loader.QueryLoader.List(ISessionImplementor session, QueryParameters queryParameters) in QueryLoader.cs: line 298
at NHibernate.Hql.Ast.ANTLR.QueryTranslatorImpl.List(ISessionImplementor session, QueryParameters queryParameters) in QueryTranslatorImpl.cs: line 119
at NHibernate.Engine.Query.HQLQueryPlan.PerformList(QueryParameters queryParameters, ISessionImplementor session, IList results) in HQLQueryPlan.cs: line 263
at NHibernate.Impl.SessionImpl.List(String query, QueryParameters queryParameters, IList results) in SessionImpl.cs: line 635
at NHibernate.Impl.SessionImpl.List(String query, QueryParameters parameters) in SessionImpl.cs: line 601
at NHibernate.Impl.QueryImpl.List() in QueryImpl.cs: line 73
at NHibernate.Impl.AbstractQueryImpl.UniqueResult() in AbstractQueryImpl.cs: line 902
at NHibernate.Test.NHSpecificTest.NewTest.Fixture.SelectScalarValueWithLock() in Fixture.cs: line 20

I am running on MS Sql Server 2008 and .net 3.5

This bug is also found i Hibernate: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2676

---

Johannes Gustafsson added a comment — 16th September 2009, 4:59:39:

A failing test

---

Stefan Agner added a comment — 3rd February 2012, 13:44:46:

Is there a workaround for this bug? If not, it would be nice this bug would get a bit higher priority...

---

Ricardo Peres added a comment — 1st September 2014, 13:39:22:

I don't think this will ever work

---

Oskar Berggren added a comment — 22nd November 2014, 15:14:12:

What would the expected SQL for "CreateQuery("select max(Id) from A a").SetLockMode("a", LockMode.Upgrade)" look like?

Perhaps something like this works as a workaround?

 var maxId = session.CreateQuery("select id from A a1 where a1.Id = (select max(Id) from A a)")
      .SetLockMode("a1", LockMode.Upgrade)
      .UniqueResult() as int?;

---

Alexander Zaytsev added a comment — 23rd November 2014, 6:58:29:

I think this is a duplicate of NH-3710 (or NH-3710 is a duplicate of this). I set it as relevant.

---

Johannes Gustafsson added a comment — 24th November 2014, 6:59:34:

The expected SQL should be something like:

select max(Id) from A as a with (updlock)

---

Alexander Zaytsev added a comment — 24th November 2014, 7:28:08:

This query does not make any sense in most of RDBMS'

---

Johannes Gustafsson added a comment — 24th November 2014, 7:40:04:

The syntax is for MSSQL. Unfortuately I don't know any other dialects.

[hazzik]

Related to #1101, #1352