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()
Issue
JPQL queryis mistranslated withhibernate version(5.0.10 , 5.0.11 ,5.0.12) > 5.0.9 . Thequeryalways 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 of over 48,000 articles and solutions.
Welcome! Check out the Getting Started with Red Hat page for quick tours and guides for common tasks.
