[hibernate-issues] [Hibernate-JIRA] Created: (HHH-6326) Oracle10gDialect cause "ORA-00904 - Invalid Identifier" because of wrong ordering of ANSI joins

[Mauro Castaldo (JIRA)]

Oracle10gDialect cause "ORA-00904 - Invalid Identifier" because of wrong ordering of ANSI joins
-----------------------------------------------------------------------------------------------

                 Key: HHH-6326
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-6326
             Project: Hibernate Core
          Issue Type: Bug
          Components: query-hql
    Affects Versions: 3.6.5
         Environment: Hibernate 3.6.5, Oracle Database 11g
            Reporter: Mauro Castaldo


If you try to execute this simple query
{quote}
select e.lastName, e.department.deptName, e.title from Employee e
{quote}
using Oracle10gDialect you get this:

{code:title=Output|borderStyle=solid}
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
15/06/2011 20:28:09,626 WARN  org.hibernate.util.JDBCExceptionReporter - SQL Error: 904, SQLState: 42000
15/06/2011 20:28:09,627 ERROR org.hibernate.util.JDBCExceptionReporter - ORA-00904: "EMPLOYEE0_"."TITLE_ID": invalid identifier
{code}

It seems hibernate ordering of ANSI JOIN blocks is wrong.
The correct query shoul be:
{code:title=Correct query|borderStyle=solid}
    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
{code}

You get the same error using HQL or JPQL.

These are the entities I used.

{code:title=Entities|borderStyle=solid}
@Entity
public class Employee implements java.io.Serializable {
    private int empNo;
    private String firstName;
    private String lastName;
    private Department department;
    private Title title;

    @Id
    public int getEmpNo() {
        return this.empNo;
    }

    public void setEmpNo(int empNo) {
        this.empNo = empNo;
    }


    public String getFirstName() {
        return this.firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }


    public String getLastName() {
        return this.lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }


    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "dept_no")
    public Department getDepartment() {
        return this.department;
    }

    public void setDepartment(Department department) {
        this.department = department;
    }


    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "title_id")
    public Title getTitle() {
        return this.title;
    }

    public void setTitle(Title title) {
        this.title = title;
    }

}

@Entity
public class Department implements java.io.Serializable {
    private String deptNo;
    private String deptName;


    @Id
    public String getDeptNo() {
        return this.deptNo;
    }

    public void setDeptNo(String deptNo) {
        this.deptNo = deptNo;
    }


    public String getDeptName() {
        return this.deptName;
    }

    public void setDeptName(String deptName) {
        this.deptName = deptName;
    }

}

@Entity
public class Title2 implements java.io.Serializable {


    private String id;
    private String description;

    @Id
    public String getId() {
        return this.id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }
}
{code}


-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira