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 N开发者_C百科Hibernate.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 repaired the bug using Alkampfer's solution, but I created my own SQL dialect rather than patching the NHibernate source directly:

public class Sql2008DialectWithBugFixes : MsSql2008Dialect
{
    public override SqlString GetLimitString(SqlString querySqlString, int offset, int last)
    {
        if (offset == 0)
        {
            return querySqlString.Insert(GetAfterSelectInsertPoint(querySqlString), " top " + last);
        }

        return base.GetLimitString(querySqlString, offset, last);
    }

    private static int GetAfterSelectInsertPoint(SqlString sql)
    {
        Int32 selectPosition;

        if ((selectPosition = sql.IndexOfCaseInsensitive("select distinct")) >= 0)
        {
            return selectPosition + 15; // "select distinct".Length;

        }
        if ((selectPosition = sql.IndexOfCaseInsensitive("select")) >= 0)
        {
            return selectPosition + 6; // "select".Length;
        }

        throw new NotSupportedException("The query should start with 'SELECT' or 'SELECT DISTINCT'");
    }
}

---

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.

---

Just had the same problem using a similar query which has a WITH clause.

Unfortunately, my query populates a grid, with paging, so I have to keep SetMaxResults.

My solution was to rewrite using a Derived Table:

var sql = @"with Foo(col1,col2,col3)
              as (select x1, x2, x3 from x join y blabla)
            Select col1, col2, col3 from Foo
            join B on B.col1 = Foo.col1";

becomes

var sql = @"Select col1, col2, col3 from 
           (select x1 as col1, x2 as col2, x3 as col3 
            from x join y blabla) as Foo
           join B on B.col1 = Foo.col1";

Just to allow NHibernate to insert the " TOP x " string after the "select" string (6 characters from the begining)... No comment :(

T

---

It seems that there is some strange bug in the routine used to find the place in the query to insert the TOP clause (GetAfterSelectInsertPoint ) as told by Sandor. You can fix it directly in nh source (I actually patched 2.1 version I'm using in a project, you can find details here). So if you absolutely needs to enable comments with use_sql_comments you can :)

---

I encountered this problem when upgrading from 1.2 to 3.2 (I know, BIG jump eh?).

The issue in my case was that there is a leading space in front of the select statement in the hql, e.g. String hql = " select "...

With SQL2005 Dialect, this crashes with a "System.NotSupportedException: The query should start with 'SELECT'..." message.

The solution is to

1. create a unit test that fails, a good Test Driven Developer should :)
2. remove the leading space from the " select..." statement
3. build and run the unit test

---

Just as i predicted - unbounding select is acceptable workaround.

Deleted SetMaxResults and it works.

---

We ran into this issue when upgrading to NHibernate version 3.3, but for a different reason...whitespace. We had a lot of sql strings that looked like this:

var sql = @"
select col1 from MyTable";

or:

var sql = @" select col1 from My Table";

These resulted in the "The query should start with 'SELECT' or 'SELECT DISTINCT'" errors because NHibernate doesn't trim the string before validating it.

We created a new dialect that trims the string first to get around this:

public class Sql2008DialectCustom : MsSql2008Dialect
{
  public override SqlString GetLimitString(SqlString queryString, SqlString offset, SqlString limit)
  {
    var trimmedQueryString = queryString.Trim();
    return base.GetLimitString(trimmedQueryString, offset, limit);
  }
}