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 query
is mistranslated withhibernate version
(5.0.10 , 5.0.11 ,5.0.12) > 5.0.9 . Thequery
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.