"Incorrect" sql is generated by nhibernate (sql that does not work in sql server 2005)

ICriteria criteria1 = NHibSessionManager.Instance.GetSession().CreateCriteria(typeof(Category));
criteria1.Add(Expression.IsNull("ParentId"));
criteria1.SetFirstResult(0);
criteria1.SetMaxResults(1);

Generates sql:

exec sp_executesql N'WITH query AS (SELECT TOP 1 ROW_NUMBER() OVER (ORDER BY this_.SortIndex desc) as __hibernate_row_nr__, this_.CategoryId as CategoryId20_0_,
this_.ApplicationId as Applicat2_20_0_, this_.ParentId as ParentId20_0_, this_.Name as Name20_0_, this_.Description as Descript5_20_0_, this_.Selectable as
Selectable20_0_, this_.SortIndex as SortIndex20_0_, this_.InsertStamp as InsertSt8_20_0_, this_.UpdateStamp as UpdateSt9_20_0_ FROM
CommunityFramework.dbo.cfCategory this_ WHERE this_.ParentId = @p0 ORDER BY this_.SortIndex desc) SELECT * FROM query WHERE __hibernate_row_nr__ > 0 ORDER BY
__hibernate_row_nr__',N'@p0 uniqueidentifier',@p0=NULL

The problem is that sql server does not work with ...WHERE this_.ParentId = @p0... ...N'@p0 uniqueidentifier',@p0=NULL

If I modify this sql in query analyzer to

exec sp_executesql N'WITH query AS (SELECT TOP 1 ROW_NUMBER() OVER (ORDER BY this_.SortIndex desc) as __hibernate_row_nr__, this_.CategoryId as CategoryId20_0_,
this_.ApplicationId as Applicat2_20_0_, this_.ParentId as ParentId20_0_, this_.Name as Name20_0_, this_.Description as Descript5_20_0_, this_.Selectable as
Selectable20_0_, this_.SortIndex as SortIndex20_0_, this_.InsertStamp as InsertSt8_20_0_, this_.UpdateStamp as UpdateSt9_20_0_ FROM
CommunityFramework.dbo.cfCategory this_ WHERE this_.ParentId IS NULL ORDER BY this_.SortIndex desc) SELECT * FROM query WHERE __hibernate_row_nr__ > 0 ORDER BY
__hibernate_row_nr__',N'@p0 uniqueidentifier',@p0=NULL

I get the row i asking for

Is this a bug in nhibernate or a limitation in SQL Server 2005

Hibernate version:1.2.0RC1

Hi,

I just tried it agains NH1.2RC1, and it generates the following sql(using AdventureWorks):

WITH query AS (SELECT TOP 1 ROW_NUMBER() OVER (ORDER BY CURRENT_TIME
STAMP) as __hibernate_row_nr__, this_.EmployeeId as EmployeeId0_0_, this_.Title
as Title0_0_, this_.BirthDate as BirthDate0_0_ FROM HumanResources.Employee thi
s_ WHERE this_.Title is null) SELECT * FROM query WHERE __hibernate_row_nr__ > 0
ORDER BY __hibernate_row_nr__

no error here.. maybe you can post your mappings/data schema, so that we can also reproduce.

hth

Here is the mappingfile:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-access="field.camelcase-underscore">
<class name="CF.Core.Category, CF" table="cfCategory">
<id name="Id" column="CategoryId" type="Guid" unsaved-value="00000000-0000-0000-0000-000000000000">
<generator class="guid.comb" />
</id>
<property name="ParentId" type="Guid" not-null="false" />
<property name="Name" type="String" length="256" not-null="true" />
<property name="Description" type="String" not-null="false" />
<property name="Selectable" type="Boolean" not-null="true" />
<property name="SortIndex" type="Int32" not-null="true" />
<bag name="ObjectTypes" cascade="save-update" lazy="true" inverse="true" generic="true" fetch="select">
<key column="ObjectTypeId" />
<one-to-many class="CF.Core.Data.ObjectType, CF" />
</bag>
<property name="InsertStamp" type="DateTime" not-null="true" />
<property name="UpdateStamp" type="DateTime" not-null="true" />
</class>
</hibernate-mapping>

Case closed. Bug made by me

hi andr,

Care to share what that bug was? And the resolution made?

Thanks