Hello,

 

Sorry that I posting bug here, but this link http://jira.nhibernate.org/secure/Dashboard.jspa is not working for me today.

 

I’ve meet issue when upgraded NHibernate 1.2.0.4000 to the version 1.2.1.4000

 

It seems to the that the bug is related with sort ordering on the computed rows, may be I’m wrong.

Below is the source information, which can help the nhibernate team:

 

ForumMessage.hbm.xml

<?xml version="1.0" encoding="utf-8" ?>

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">

      <class name="com.entities.ForumMessage, com.entities" table="forum_message">

            <id name="Id" type="Guid" unsaved-value="00000000-0000-0000-0000-000000000000">

                  <column name="msg_id" sql-type="uniqueidentifier" not-null="true" unique="true" index="PK_ForumMessage"/>

      <generator class="guid" />

    </id>

            <property name="Subject" type="String">

                  <column name="subject" sql-type="nvarchar" not-null="false"/>

            </property>

            <property name="Text" type="String">

                  <column name="text" sql-type="nvarchar" not-null="false"/>

            </property>

            <property name="CreationTime" type="DateTime">

                  <column name="creation_time" length="8" sql-type="datetime" not-null="true"/>

            </property>

            <property name="Tag" type="String">

                  <column name="tag" sql-type="nvarchar" not-null="false"/>

            </property>

    <property name="Closed">

      <column name="closed"/>

    </property>

     

 

   

            <many-to-one name="Forum" class="com.entities.Forum, com.entities" >

                  <column name="forum_id" not-null="true"/>

            </many-to-one>

            <many-to-one name="Parent" class="com.entities.ForumMessage, com.entities">

                  <column name="parent_id" sql-type="uniqueidentifier" not-null="false"/>

            </many-to-one>

            <bag name="Children" inverse="true" cascade="all-delete-orphan" lazy="true">

                  <key column="parent_id"/>

                  <one-to-many class="com.entities.ForumMessage, com.entities"/>

            </bag>

            <many-to-one name="User" class="com.entities.User, com.entities">

                  <column name="user_id" length="8" sql-type="bigint" not-null="true"/>

            </many-to-one>

 

 

    <!--Total posts count-->

    <property name="PostCount"

              formula="(select count(1) from forum_message fm

                                         where fm.parent_id=msg_id)

                       "/>

    <!--Last post creation time-->

    <property name="LastPostCreationTime"

         formula="(select max(fm.creation_time)

                    from forum_message fm

                    where fm.parent_id=msg_id

                        or fm.msg_id=msg_id

                                     )

                       "/>

    <!--Last post user name-->

    <property name="LastPostUserName"

         formula="(select top(1) up.nickname

            from forum_message  fm

                                   join user_profile up on up.user_uid=fm.user_id

                             where fm.parent_id=msg_id

                or fm.msg_id=msg_id

                             order by fm.creation_time desc

                             )

                       "/>

   

  </class>

  <query name="SearchSubjectQuery">

    SELECT forumMessage FROM ForumMessage forumMessage, Forum forum, User user

    WHERE forumMessage.Forum=forum

    AND forumMessage.Parent is null

    AND forumMessage.User=user

    AND forum.Tablet = :tablet

    AND (

      forumMessage.Subject like :searchString

      OR forumMessage.Text like :searchString

      OR forumMessage.Tag like :searchString

      OR user.Nickname like :searchString

      OR forumMessage.CreationTime like :searchString

        )

  </query>

 

  <sql-query name="GetSubjectOrdinal" >

      <return-scalar column="RowNumber" type="Int32" />

      exec GetSubjectOrdinal :subjectId, :order

  </sql-query>

    <!--Get subject ordinal number by msg_id--> 

  <sql-query name="GetSubjectNumberSqlQuery" >

    <return-scalar column="RowNumber" type="Int32" />

    select RowNumber-1 as RowNumber from(

    select ROW_NUMBER() OVER(ORDER BY :orderBy ) AS RowNumber,*

    from(

    select msg_id,[subject],parent_id,

    (

    select count(1) from forum_message fm

    where fm.parent_id=forum_message_main.msg_id

    ) as postCount,

    (

    select max(fm.creation_time)

    from forum_message fm

    where fm.parent_id=forum_message_main.msg_id

    or fm.msg_id=forum_message_main.msg_id

    )as LastPostCreationTime

    from forum_message forum_message_main

    where forum_message_main.forum_id=:forum_id

    and forum_message_main.parent_id is null

 

    ) message

    )message1

    where message1.msg_id=:message_id

  </sql-query>

</hibernate-mapping>

 

Forum.hbm.xml

<?xml version="1.0" encoding="utf-8" ?>

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">

      <class name="com.entities.Forum, com.entities" table="forum">

    <id name="Id" type="Guid" unsaved-value="00000000-0000-0000-0000-000000000000">

      <column name="forum_id"   sql-type="uniqueidentifier" not-null="true" unique="true" index="PK_forum"/>

      <generator class="guid" />

    </id>

            <property name="Title" type="String">

                  <column name="title" sql-type="nvarchar" not-null="true"/>

            </property>

    <property name="CreationTime" >

      <column name="creation_time"  not-null="true"/>

    </property>

 

    <property name="Description" type="String">

      <column name="description" sql-type="nvarchar" not-null="true"/>

    </property>

    <many-to-one name="Tablet" class="com.entities.Tablet, com.entities">

      <column name="tablet_id" length="8" sql-type="bigint" not-null="true"/>

    </many-to-one>

            <many-to-one name="User" class="com.entities.User, com.entities">

                  <column name="user_id" length="8" sql-type="bigint" not-null="true"/>

            </many-to-one>

            <bag name="Messages" inverse="true"  cascade="all-delete-orphan" lazy="true" where="parent_id is null">

                  <key column="forum_id" foreign-key="FK_forum_message_forum"   />

                  <one-to-many class="com.entities.ForumMessage, com.entities" />

            </bag>

 

  <!--Total posts count-->

    <property name="PostCount"

              formula="(select count(1) from forum_message fm

                                         where fm.forum_id=forum_id

                        and fm.parent_id is not null

                        )

                       "/>

    <property name="LastPostCreationTime"

         formula="(

              select top(1) isnull(fm.creation_time,forum.creation_time)  from forum

                    left outer join forum_message fm

                                on fm.forum_id=forum.forum_id

              where forum.forum_id=forum_id

              order by(isnull(fm.creation_time,forum.creation_time)) desc

                             )

                       "/>

    <property name="LastPostUserName"

         formula="(

              select top(1) up.nickname  from forum

                    left outer join forum_message fm

                                on fm.forum_id=forum.forum_id

                    join user_profile up on up.user_uid=(isnull(fm.user_id,forum.user_id))

              where forum.forum_id=forum_id

              order by(isnull(fm.creation_time,forum.creation_time)) desc

                             )

                       "/>

             

   

      </class>

 

  <!--Use this query to avoid deleting every message individually by hibernate

  All messages will be deleted cascade-->

  <sql-query name="DeleteMessageBoardForum" >

    <return-scalar  column="Count" type="Int32"/>

      DELETE FROM forum WHERE forum_id=:forum_id

  </sql-query>

</hibernate-mapping>

 

 

The C# code snippet:

 

            ICriteria criteria = session.CreateCriteria(typeof (ForumMessage))

                .Add(new EqExpression("Forum", forum))

                .Add(new NullExpression("Parent"))

                .AddOrder(new Order(sortString, isAscending))

                .SetFirstResult(firstMessage)

                .SetMaxResults(messageCount);

           

            return criteria.List<ForumMessage>();

 

The generated sql-query is (red text has compile error bug):

 

 

SELECT TOP 11 msg1_5_1_, subject5_1_, text5_1_, creation4_5_1_, tag5_1_, closed5_1_, forum7_5_1_, parent8_5_1_, user9_5_1_, formula0_1_, formula1_1_, formula2_1_, user1_13_0_, nickname13_0_, user3_13_0_, primary4_13_0_, alternate5_13_0_, first6_13_0_, last7_13_0_, birthday13_0_, address9_13_0_, address10_13_0_, city13_0_, zip12_13_0_, home13_13_0_, office14_13_0_, mobile15_13_0_, alternate16_13_0_, last17_13_0_, is18_13_0_, language19_13_0_, country20_13_0_, state21_13_0_, invalid22_13_0_, last23_13_0_, website13_0_, aboutme13_0_, datefmt13_0_, timefmt13_0_, working28_13_0_, working29_13_0_, firstda30_13_0_, external31_13_0_, external32_13_0_, date33_13_0_, persona34_13_0_, timezone35_13_0_ FROM (SELECT ROW_NUMBER() OVER(ORDER BY __hibernate_sort_expr_1__ DESC) as row, query.msg1_5_1_, query.subject5_1_, query.text5_1_, query.creation4_5_1_, query.tag5_1_, query.closed5_1_, query.forum7_5_1_, query.parent8_5_1_, query.user9_5_1_, query.formula0_1_, query.formula1_1_, query.formula2_1_, query.user1_13_0_, query.nickname13_0_, query.user3_13_0_, query.primary4_13_0_, query.alternate5_13_0_, query.first6_13_0_, query.last7_13_0_, query.birthday13_0_, query.address9_13_0_, query.address10_13_0_, query.city13_0_, query.zip12_13_0_, query.home13_13_0_, query.office14_13_0_, query.mobile15_13_0_, query.alternate16_13_0_, query.last17_13_0_, query.is18_13_0_, query.language19_13_0_, query.country20_13_0_, query.state21_13_0_, query.invalid22_13_0_, query.last23_13_0_, query.website13_0_, query.aboutme13_0_, query.datefmt13_0_, query.timefmt13_0_, query.working28_13_0_, query.working29_13_0_, query.firstda30_13_0_, query.external31_13_0_, query.external32_13_0_, query.date33_13_0_, query.persona34_13_0_, query.timezone35_13_0_, query.__hibernate_sort_expr_1__ FROM (SELECT this_.msg_id as msg1_5_1_, this_.subject as subject5_1_, this_.text as text5_1_, this_.creation_time as creation4_5_1_, this_.tag as tag5_1_, this_.closed as closed5_1_, this_.forum_id as forum7_5_1_, this_.parent_id as parent8_5_1_, this_.user_id as user9_5_1_, (select count(1) from forum_message fm

                                         where fm.parent_id=this_.msg_id)

                        as formula0_1_, (select max(fm.creation_time)

                    from forum_message fm

                    where fm.parent_id=this_.msg_id

                        or fm.msg_id=this_.msg_id

                                     )

                        as formula1_1_, (select top(1) up.nickname

            from forum_message  fm

                                   join user_profile up on up.user_uid=fm.user_id

                             where fm.parent_id=this_.msg_id

                or fm.msg_id=this_.msg_id

                             order by fm.creation_time desc

                             )

as formula2_1_, user2_.user_uid as user1_13_0_,

user2_.nickname as nickname13_0_,

user2_.user_password as user3_13_0_,

user2_.primary_email as primary4_13_0_,

user2_.alternate_email as alternate5_13_0_, user2_.first_name as first6_13_0_, user2_.last_name as last7_13_0_,

user2_.birthday as birthday13_0_, user2_.address_line1 as address9_13_0_, user2_.address_line2 as address10_13_0_,

user2_.city as city13_0_, user2_.zip_code as zip12_13_0_, user2_.home_phone as home13_13_0_,

user2_.office_phone as office14_13_0_, user2_.mobile_phone as mobile15_13_0_,

user2_.alternate_phone as alternate16_13_0_, user2_.last_login as last17_13_0_,

user2_.is_approved as is18_13_0_, user2_.language_id as language19_13_0_, user2_.country_id as country20_13_0_,

user2_.state_id as state21_13_0_, user2_.invalid_login_attempts as invalid22_13_0_, user2_.last_invalid_attempt as last23_13_0_, user2_.website as website13_0_, user2_.aboutme as aboutme13_0_, user2_.datefmt as datefmt13_0_, user2_.timefmt as timefmt13_0_, user2_.workingdaystart as working28_13_0_, user2_.workingdayend as working29_13_0_, user2_.firstdayofweek as firstda30_13_0_, user2_.external_alerts_byemail as external31_13_0_, user2_.external_alerts_bymobile as external32_13_0_,

user2_.date_created as date33_13_0_, user2_.personalization_id as persona34_13_0_, user2_.timezone_id as timezone35_13_0_, (

 

select as __hibernate_sort_expr_1__

      FROM dbo.forum_message this_

      inner join dbo.user_profile user2_ on this_.user_id=user2_.user_uid

      WHERE this_.forum_id = @p0 and this_.parent_id is null) query )

page WHERE page.row > 0 ORDER BY __hibernate_sort_expr_1__ DESC

 

SQL error text:

Incorrect syntax near the keyword 'as'.