Hibernate generates incorrect sql query if more than two levels of references in hql being used. For example: select e.lastName, e.department.deptName, e.title from Employee e The following sql is generated by Hibernate: select employee0_.lastName as col_0_0_, department1_.deptName as col_1_0_, employee0_.title_id as col_2_0_, title2_.id as id1_, title2_.description as descript2_1_ from Employee employee0_, Department department1_ inner join Title title2_ on employee0_.title_id=title2_.id where employee0_.dept_no=department1_.deptNo If the query above is being executed "invalid identifier" error is thrown at the database such as oracle, postgres, etc. The correct query should be like: select employee0_.lastName as col_0_0_, department1_.deptName as col_1_0_, employee0_.title_id as col_2_0_, title2_.id as id1_, title2_.description as descript2_1_ from Employee employee0_ inner join Title title2_ on employee0_.title_id=title2_.id, Department department1_ where employee0_.dept_no=department1_.deptNo Please note that, the hibernate generated sql put the line "department department1_ " prior to the inner join: select ... from Employee employee0_, Department department1_ inner join .... where ... But actually the correct sql should be like: select ... from Employee employee0_ inner join Title title2_ on employee0_.title_id=title2_.id, Department department1_ where ... You can find more details from https://hibernate.atlassian.net/browse/HHH-6326
more use cases: @NamedQuery(name = "AgendaRecolhimento.queryPesquisa", query = "select new AgendaRecolhimento(ar.id, ar.convenio.id, ar.convenio.descricao, ar.dataReferencia, ar.numAgenda, ar.situacaoAgenda.descricao, ar.situacaoAgenda.codigoRegistro) from AgendaRecolhimento ar order by ar.dataReferencia, ar.numAgenda, ar.convenio.id"), @NamedQuery(name = "Debito.queryPesquisa", query = "select new Debito(d.id, d.idDebitoOriginal, d.anoDebito, d.mesDebito, d.indDividaAtiva, d.vlPrincipal, d.situacaoDebito.id, d.situacaoDebito.descricao, responsavelDebito, t.nome, o.sigla, i.inscricaoImovel, i.digito) from Debito d join d.responsavelDebito as responsavelDebito left outer join d.tributoOrgao as tributoOrgao left outer join tributoOrgao.orgao o left outer join tributoOrgao.tributoArr t left outer join d.imovel as i"), @NamedQuery(name = "TributoOrgao.queryLookup", query = "select new TributoOrgao(tor.id, tor.orgao.id, tor.orgao.descricao, tor.orgao.sigla, tor.tributoArr.id, tor.tributoArr.nome) from TributoOrgao tor") @NamedQuery(name = "TributoReceita.queryPesquisa", query = "select new TributoReceita(tr.id, tr.tributoArr.id, tr.tributoArr.codigo, tr.tributoArr.descCompleta, tr.receitaContabil.exercicio, tr.receitaContabil.codigoReceita, tr.receitaContabil.nome, tr.tipoReceitaTributo.descricao, tr.dataInicio, tr.dataFim) from TributoReceita tr order by tr.tributoArr.codigo, tr.tributoArr.descCompleta"),
Created attachment 917174 [details] hibernate-prjs.zip
This is a regression. The fix for https://hibernate.atlassian.net/browse/HHH-3749 was applied to 3.2 and 3.3 branches properly, but was only partially fixed in the trunk (which ultimately was branched for 3.5). There were 2 commits for 3.3: 15867 (https://github.com/hibernate/hibernate-orm/commit/89cf22bab6bbd5aa317ca7a1df63624bb7b677c4) 15873 (https://github.com/hibernate/hibernate-orm/commit/7b943f0089f6f90cbf836b499e2da7c1ac9e9dea) There was only 1 commit for trunk, which corresponds to the first commit on 3.3 (15867): 15866 (https://github.com/hibernate/hibernate-orm/commit/4ed21cbce5057cfdaf637bd7e98b3788f87e035d) When I made the changes to 4.3 from the 2nd commit on 3.3, the resulting SQL was the same as from 3.3.2.GA. There were no failures due to this change to 4.3, but I need to check the affect on generated SQL from the tests to ensure that differences are as expected. I'll do this tomorrow.
I need to clarify that the regression is not in HHH-6326. That was also present in 3.3.2.GA. The regression is that an extra join that was basically the same as an existing join was being generated . It was this extra join that was causing the situation seen with HHH-6326. The fix gets rid of this extra join, making the generated SQL the same as it was in 3.3.2.GA. I'll create a new Jira issue for this fix.
I've created a new issue for the regression: https://hibernate.atlassian.net/browse/HHH-9305 HHH-6326 is a long-standing issue and will not be fixed by HHH-9305; however, the fix for HHH-9305 will avoid running into HHH-6326 in most of the cases listed here. The following HQL will still be problematic because it has >1 implicit join: select e.lastName, e.department.deptName, e.title from Employee e After HHH-9305 is fixed, it will be easier to workaround by making 1 or both implicit joins explicit instead. For example: select e.lastName, e.department.deptName, e.title from Employee e inner join e.department select e.lastName, e.department.deptName, e.title from Employee e inner join e.title select e.lastName, e.department.deptName, e.title from Employee e inner join e.department inner join e.title
Gary, I've changed the target release to EAP 6.3.1. Please add a new BZ for EAP 6.4.0. Thanks! Gail
HHH-9305 has been fixed in the upstream (4.2) branch.
*** Bug 1077318 has been marked as a duplicate of this bug. ***
Verified in EAP 6.3.1.CR1.