Hibernate version: 2.0 Alpha 1

[Name and version of the database you are using: SQL Server 2000

The generated SQL (show_sql=true):

NHibernate.SQL: 2008-04-17 08:45:41,596 [4] DEBUG NHibernate.SQL - SELECT top 30 substring(this_.ACTIVITY, @p0, @p1) as y0_ FROM lawson.ACACTIVITY this_ WHERE this_.ACTIVITY_GRP = @p2 and this_.ACTIVITY like @p3 and not this_.ACTIVITY like @p4; @p0 = 'MONITOR', @p1 = '%-CONT%', @p2 = '000%', @p3 = '1', @p4 = '7'

I'm running the following query to get the first 30 distinct values of the first 7 characters of ID from the ACTIVITY table where the Id's don't start with '000' and end with '-CONT'.

NHibernate.ICriteria query = SessionManager.CurrentSession.CreateCriteria(typeof(Activity))
.SetProjection(
new SqlFunctionProjection("substring",
NHibernate.NHibernateUtil.String,
Projections.Property("Id"),
new ConstantProjection(1),
new ConstantProjection(7)
).As("ShortId"))
.Add(Expression.Eq("ActivityGroup", "MONITOR"))
.Add(Expression.Like("Id", "%-CONT%"))
.Add(Expression.Not(Expression.Like("Id", "000%")))
.SetResultTransformer(CriteriaSpecification.DistinctRootEntity)
.SetMaxResults(30);

The query is failing because the start and length parameters of the substring function are @p3 and @p4, but the function is being invoked as substring(this_.ACTIVITY, @p0, @p1), i.e., it's referencing the wrong parameters.

Here are my questions:

1) I'm not very familiar with projections - is this the appropriate way to perform a substring? If not, what's the preferred method?
2) Is this a bug?

Thanks.

Hi Mike,

I found some scenarios where the parameter order gets messed up. Most of the time it's caused by mixing named and positional parameters. I've created a patch (NH-1098) fixing some of the causes, but not all. The patch isn't in alpha 1, but it's in 2.0.x. In your example, I wouldn't expect my patch to help, but you may want to check it anyway.

Thaks. I'll get 2.0.x and try it when I get a chance. It won't be for a couple of weeks, because I have to go out of town for a while. I'll pos any results of further testing.

Thaks. I'll get 2.0.x and try it when I get a chance. It won't be for a couple of weeks, because I have to go out of town for a while. I'll pos any results of further testing.