JPQL query mistranslated with hibernate version(5.0.10 , 5.0.11 ,5.0.12) > 5.0.9. The query always works for the first page, but fails when using pagination setFirstResult()

Solution Unverified - Updated -

Issue

  • JPQL query is mistranslated with hibernate version(5.0.10 , 5.0.11 ,5.0.12) > 5.0.9 . The query always works for the first page, but fails when using pagination setFirstResult().
  • SQLServer2005LimitHandler uncapable of handle subquery in column list.

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.10 version generated 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_'.

Environment

  • Red Hat JBoss Enterprise Application Platform (EAP)
    • 7.0

Subscriber exclusive content

A Red Hat subscription provides unlimited access to our knowledgebase, tools, and much more.

Current Customers and Partners

Log in for full access

Log In

New to Red Hat?

Learn more about Red Hat subscriptions

Using a Red Hat product through a public cloud?

How to access this content