1:40 p.m.
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira