title: Fluent nHibernate generates wrong query
body: <p>I'm trying to use pagination for a query. It works when i request the first page but if i request any page after that i'm getting an error.</p>
  
  <pre><code>public ProgramMemberResultDTO GetSentFromList(SegmentFilterDTO filter)
  {
          var sql = @"SELECT a.Email as Email, a.Phone as Cellphone, mp.mpFirstname as Firstname, mp.mpLastname as Lastname, ISNULL(b.ID, 0) as IsSent FROM
                          ListMember a LEFT JOIN
                          (SELECT this_.ID, this_.Email as Email, this_.Phone as Cellphone
                              FROM ListMember this_ 
                              left join SMSOutgoing sms on sms.PhoneNumber = this_.Phone 
                              left join CampaignSMS cs on cs.SMSID = sms.ID
                              WHERE this_.ListID = :listId AND cs.CampaignListID = :campaignListId) b on a.ID = b.ID
                              INNER JOIN MemberPrograms mp on a.ProgramProfileID = mp.ID
                              WHERE a.ListID = :listId";
  
          var criteria = _session.CreateSQLQuery(sql);
  
          criteria.SetParameter("campaignListId", filter.CampaignListId);
          criteria.SetParameter("listId", filter.SelectedList);
  
          var records = criteria.SetFirstResult(filter.PageNr)
              .SetMaxResults(filter.ResultsPerPage)
              .SetResultTransformer(Transformers.AliasToBean(typeof(ProgramMemberDTO))).List&lt;ProgramMemberDTO&gt;();
  
          var result = new ProgramMemberResultDTO()
          {
              Count = GetNrOfSentFromList(filter.CampaignListId, filter.SelectedList),
              PageNr = filter.PageNr,
              Rows = records
          };
  
          return result;
  }
  </code></pre>
  
  <p>It seems nHibernate is generating an invalid query that the database can't run. This is the query it generates: </p>
  
  <blockquote>
    <p>SELECT TOP (@p0) FROM (, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row SELECT a.Email as Email, a.Phone as Cellphone, mp.mpFirstname as Firstname, mp.mpLastname as Lastname, ISNULL(b.ID, 0) as IsSent FROM ListMember a LEFT JOIN (SELECT this_.ID, this_.Email as Email, this_.Phone as Cellphone FROM ListMember this_ left join SMSOutgoing sms on sms.PhoneNumber = this_.Phone left join CampaignSMS cs on cs.SMSID = sms.ID WHERE this_.ListID = @p1 AND cs.CampaignListID = @p2) b on a.ID = b.ID INNER JOIN MemberPrograms mp on a.ProgramProfileID = mp.ID WHERE a.ListID = @p1) as query WHERE query.__hibernate_sort_row > @p4 ORDER BY query.__hibernate_sort_row &lt;</p>
  </blockquote>
  
  <p><strong>EDIT</strong></p>
  
  <p>This is the error message:</p>
  
  <blockquote>
    <p>"could not execute query
    ↵[ SELECT TOP (@p0)  FROM (, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row SELECT a.Email as Email, a.Phone as Cellphone, mp.mpFirstname as Firstname, mp.mpLastname as Lastname, ISNULL(b.ID, 0) as IsSent FROM
    ↵                            ListMember a LEFT JOIN
    ↵                            (SELECT this_.ID, this_.Email as Email, this_.Phone as Cellphone
    ↵                                FROM ListMember this_ 
    ↵                                left join SMSOutgoing sms on sms.PhoneNumber = this_.Phone 
    ↵                                left join CampaignSMS cs on cs.SMSID = sms.ID
    ↵                                WHERE this_.ListID = @p1 AND cs.CampaignListID = @p2) b on a.ID = b.ID
    ↵                                INNER JOIN MemberPrograms mp on a.ProgramProfileID = mp.ID
    ↵                                WHERE a.ListID = @p1) as query WHERE query.__hibernate_sort_row > @p4 ORDER BY query.__hibernate_sort_row ]
    ↵  Name:campaignListId - Value:172  Name:listId - Value:110
    ↵[SQL: SELECT TOP (@p0)  FROM (, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row SELECT a.Email as Email, a.Phone as Cellphone, mp.mpFirstname as Firstname, mp.mpLastname as Lastname, ISNULL(b.ID, 0) as IsSent FROM
    ↵                            ListMember a LEFT JOIN
    ↵                            (SELECT this_.ID, this_.Email as Email, this_.Phone as Cellphone
    ↵                                FROM ListMember this_ 
    ↵                                left join SMSOutgoing sms on sms.PhoneNumber = this_.Phone 
    ↵                                left join CampaignSMS cs on cs.SMSID = sms.ID
    ↵                                WHERE this_.ListID = @p1 AND cs.CampaignListID = @p2) b on a.ID = b.ID
    ↵                                INNER JOIN MemberPrograms mp on a.ProgramProfileID = mp.ID
    ↵                                WHERE a.ListID = @p1) as query WHERE query.__hibernate_sort_row > @p4 ORDER BY query.__hibernate_sort_row]"</p>
  </blockquote>
  
  <p>Any ideas?</p>