NHibernate 2nd lvl cache, custom query, sqldialect

I got trunk version of NH and FNH. When i try to add 2nd level cache, some parts of NHibernate forgets about chosen sqldialect.

---

Initial configuration:

var cfg = Fluently.Configure()
  .Database(MsSqlConfiguration.MsSql2008
    .ConnectionString(connectionString)
    .DefaultSchema("dbo")
    .UseReflectionOptimizer()    
  .Mappings(m => ................);

Guilty custom query:

var sql = @"with Foo(col1,col2,col3)
              as (select bla bla bla...)
            Select bla bla bla from Foo";

list = Session.CreateSQLQuery(sql)
  .AddEntity("fizz", typeof(Fizz))
  .SomethingUnimportant();

When i change configuration to:

var cfg = Fluently.Configure()
  .Database(MsSqlConfiguration.MsSql2008
    .ConnectionString(connectionString)
    .DefaultSchema("dbo")
     .UseReflectionOptimizer()
     .Cache(c=>c
       .UseQueryCache()
         .ProviderClass<HashtableCacheProvider>())
       .ShowSql())
     .Mappings(m => ................);

Query throws error (WITH clause was added in mssql2008):

The query should start with 'SELECT' or 'SELECT DISTINCT'

[NotSupportedException: The query should start with 'SELECT' or 'SELECT DISTINCT'] NHibernate.Dialect.MsSql2000Dialect.GetAfterSelectInsertPoint(SqlString sql) +179 NHibernate.Dialect.MsSql2000Dialect.GetLimitString(SqlString querySqlString, Int32 offset, Int32 limit) +119 NHibernate.Dialect.MsSql2005Dialect.GetLimitString(SqlString querySqlString, Int32 offset, Int32 last) +127 NHibernate.Loader.Loader.PrepareQueryCommand(QueryParameters queryParameters, Boolean scroll, ISessionImplementor session) +725 NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) +352 NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) +114 NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) +205

---

Any ideas what exactly confuses nhibernate and how to fix it?

---

Guilty NHibernate code (in NHibernate/Dialect/MsSql200Dialect.cs):

private static int GetAfterSelectInsertPoint(SqlString sql)
{
  if (sql.StartsWithCaseInsensitive("select distinct"))
  {
    return 15;
  }
  else if (sql.StartsWithCaseInsensitive("select"))
  {
    return 6;
  }
  throw new NotSupportedException
    ("The query should start with 'SELECT' or 'SELECT DISTINCT'");
  }
}

Looks that .SetMaxResults(123) causes this. Fortunately, i can unbound that query.

Hopefully that will fix this.

I had a similar issue (removing SetMaxResults also helped but I needed paging) and found out that the following NHibernate configuration property was causing this bug:

<property name="use_sql_comments">true</property>

It's certainly a bug, because the GetAfterSelectInsertPoint method doesn't take into account that SQL comments may be prepended to the SQL query.

Just set the use_sql_comments property to false and the problem disappears.