With HIbernate 5.0.9 and MS sql database the below query works fine. But with hibernate version 5.0.11 or 5.0.12 the same query is working for the first page, but it fails when we use setFirstResult() because of method "fillAliasInSelectClause(), shallowIndexOfPattern() " code change in SQLServer2005LimitHandler .

With hibernate 5.0.9 version generated query
Hibernate:
/* select
e.id,
(select
count(a.id) as foo
from
Address a
inner join
a.employee ae
where
(
ae.id = e.id
and a.city = 'pune'
and ae.job='SALESMAN'
)
or (
a.country.code in (
'USA', 'CHE'
)
)) as vcc,
e.name,
addr.country.code
from
Employee e
left join
e.addresses addr
where
(
select
count(a.id) as foo
from
Address a
where
a.employee.id = e.id
) > 0 */ WITH query AS (SELECT
inner_query.*,
ROW_NUMBER() OVER (
ORDER BY
CURRENT_TIMESTAMP) as _hibernate_row_nr_
FROM
( select
employee0_.EMPNO as col_0_0_,
(select
count(address2_.address_id)
from
ADDRESS address2_
inner join
EMPLOYEE employee3_
on address2_.EMPNO=employee3_.EMPNO
where
employee3_.EMPNO=employee0_.EMPNO
and address2_.city='pune'
and employee3_.JOB='SALESMAN'
or address2_.COUNTRY_CODE in ('USA' , 'CHE')) as col_1_0_,
employee0_.ENAME as col_2_0_,
addresses1_.COUNTRY_CODE as col_3_0_
from
EMPLOYEE employee0_
left outer join
ADDRESS addresses1_
on employee0_.EMPNO=addresses1_.EMPNO
where
(select
count(address4_.address_id)
from
ADDRESS address4_
where
address4_.EMPNO=employee0_.EMPNO)>0 ) inner_query ) SELECT
col_0_0_,
col_1_0_,
col_2_0_,
col_3_0_
FROM
query
WHERE
_hibernate_row_nr_ >= ?
AND _hibernate_row_nr_ < ?

With HIbernate 5.0.11 or 5.0.12 version genarated query
Hibernate:
/* select
e.id,
(select
count(a.id) as foo1
from
Address a
inner join
a.employee ae
where
(
ae.id = e.id
and a.city = 'pune'
and ae.job='SALESMAN'
)
or (
a.country.code in (
'USA', 'CHE'
)
)) as vcc,
e.name,
addr.country.code
from
Employee e
left join
e.addresses addr
where
(
select
count(a.id) as foo
from
Address a
where
a.employee.id = e.id
) > 0 */ WITH query AS (SELECT
inner_query.*,
ROW_NUMBER() OVER (
ORDER BY
CURRENT_TIMESTAMP) as _hibernate_row_nr_
FROM
( select
employee0_.EMPNO as col_0_0_,
(select
count(address2_.address_id) as page0_
from
ADDRESS address2_
inner join
EMPLOYEE employee3_
on address2_.EMPNO=employee3_.EMPNO
where
employee3_.EMPNO=employee0_.EMPNO
and address2_.city='pune'
and employee3_.JOB='SALESMAN'
or address2_.COUNTRY_CODE in ('USA' , 'CHE')) as col_1_0_,
employee0_.ENAME as col_2_0_,
addresses1_.COUNTRY_CODE as col_3_0_
from
EMPLOYEE employee0_
left outer join
ADDRESS addresses1_
on employee0_.EMPNO=addresses1_.EMPNO
where
(select
count(address4_.address_id)
from
ADDRESS address4_
where
address4_.EMPNO=employee0_.EMPNO)>0 ) inner_query ) SELECT
col_0_0_,
page0_
FROM
query
WHERE
_hibernate_row_nr_ >= ?
AND _hibernate_row_nr_ < ?
May 01, 2017 11:51:48 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
WARN: SQL Error: 207, SQLState: 42S22
May 01, 2017 11:51:48 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: Invalid column name 'page0_'.