Bug 1128278

+++ This bug was initially created as a clone of Bug #1092175 +++

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

--- Additional comment from Gary Hu on 2014-06-23 15:16:19 EDT ---

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"),

--- Additional comment from Gary Hu on 2014-07-10 16:26:48 EDT ---



--- Additional comment from Gary Hu on 2014-07-10 16:31:20 EDT ---

Hi Gail,

The customer has provided another example that shows the regression. 

The attached zip contains two projects one works fine with hibernate 3.3.1.GA and the other one fails with hibernate 4.

I've also tested their projects. I can verify it works fine with 3.3.2.GA and start failing with hibernate 3.5.0-Final. I believe some change made in 3.5.0-Final causes the regression.

It's this hql that shows the issue:
"select new Employee(e.id, e.lastName, e.title.id, e.title.description, e.department, e.firstName) from Employee e inner join e.department"

--- Additional comment from Gary Hu on 2014-07-10 16:51:25 EDT ---

I changed the pom.xml in the attached hibernate4 project to use 3.5.0-Final:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.redhat.support</groupId>
    <artifactId>hibernate4.test.01076083</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <properties>
        <!-- eap 6.1.1 -->
        <version.org.jboss.bom>1.0.4.Final-redhat-9</version.org.jboss.bom>
    </properties>
    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.jboss.bom</groupId>
                <artifactId>jboss-javaee-6.0-with-hibernate</artifactId>
                <version>${version.org.jboss.bom}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>
    <dependencies>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>3.5.0-Final</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-entitymanager</artifactId>
            <version>3.5.0-Final</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.11</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>9.3-1100-jdbc4</version>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>1.6.1</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-log4j12</artifactId>
            <version>1.6.5</version>
        </dependency>
    </dependencies>
    

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>2.0.2</version>
                <configuration>
                    <source>1.6</source>
                    <target>1.6</target>
                </configuration>
            </plugin>
        </plugins>
    </build>
    
</project>

--- Additional comment from Gail Badner on 2014-07-16 02:56:22 EDT ---

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.

--- Additional comment from Gail Badner on 2014-07-16 18:06:55 EDT ---

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.

--- Additional comment from Gail Badner on 2014-07-29 20:04:18 EDT ---

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

--- Additional comment from Gail Badner on 2014-07-29 20:06:50 EDT ---

Gary, I've changed the target release to EAP 6.3.1.

Please add a new BZ for EAP 6.4.0.

Thanks!
Gail

--- Additional comment from Gail Badner on 2014-07-30 03:46:42 EDT ---

HHH-9305 has been fixed in the upstream (4.2) branch.

--- Additional comment from mark yarborough on 2014-08-04 09:23:55 EDT ---

Triage: GSS will clean/link/dup as appropriate and then we'll re-triage 
whichever bug approp for this streams...