title: JPA / Hibernate pagination in MS SQL Server 2008 eror mapping to object
body: <p>Given my code below:</p>
  
  <pre><code>Query q = em.createNativeQuery(sql.toString(), SearchDTO.class);
  for (String k : parameters.keySet()) {
      q.setParameter(k, parameters.get(k));           
  }
  q.setFirstResult((criteria.getPage()-1) * criteria.getLimit());
  q.setMaxResults(criteria.getLimit());       
  
  return q.getResultList();
  </code></pre>
  
  <p>Where page is > 1, the sql generated is incorrect:</p>
  
  <p>WITH query AS (SELECT inner_query.*, ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as <strong>hibernate_row_nr</strong> FROM ( select TOP(?) cc.company_id as <strong>page0_</strong>, cc.long_name as <strong>page1_</strong>, cc.reuters_org_id as <strong>page2_</strong>, ccdom.country_name as country_of_domicile, ccinc.country_name as country_of_incorporation, ccr.region_name as region, ci.industry_name as industry from zz_prp_common_company cc left join zz_prp_common_country ccdom on cc.country_of_domicile = ccdom.country_id left join zz_prp_common_region ccr on ccr.region_id = ccdom.region_id left join zz_prp_common_country ccinc on cc.country_of_domicile = ccinc.country_id left join zz_prp_common_industry ci on cc.industry_id = ci.industry_id where 1=1 order by cc.long_name ) inner_query ) <strong>SELECT page0_, page1_, page2_</strong>, country_of_domicile, country_of_incorporation, region, industry FROM query WHERE <strong>hibernate_row_nr</strong> >= ? AND <strong>hibernate_row_nr</strong> &lt; ?</p>
  
  <p>I cannot understand why it is replacing the alias to my columns to page0_, page1_ and page2_. Due to this the where page0_ replaced the company_id column alias, I am getting this error:</p>
  
  <pre><code>Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The column name company_id is not valid.
  </code></pre>
  
  <p>I am using MS SQL Server 2008 R2, and I have setup my hibernate dialect to use org.hibernate.dialect.SQLServer2008Dialect.</p>