Hi,
i am using toplink essentials (Build 060829) and the latest build(V2_build_39)
i found toplink essentials generate incorrect SQL under the following situation:
@Entity
@Table(name="project")
@IdClass(xxx.class)
@SecondaryTables
(
{ @SecondaryTable(name="project_detail1",
pkJoinColumns={
@PrimaryKeyJoinColumn(name="project_id",referencedColumnName="project_id"),
@PrimaryKeyJoinColumn(name="dept_id", referencedColumnName="dept_id")
})
class Project
{
@Id
@Column(name="project_id")
String projectID;
@Id
@Column(name="dept_id")
String deptID;
@Column(table="project_detail1", name="name")
String name;
.....
.....
}
@Entity
@Table(name="audit_log")
class AuditLog
{
@Id
@Column(name="log_id")
int logID;
@Column(name="project_id")
String projectID;
@Column(name="dept_id")
String deptID;
@ManyToOne
@JoinColumns
(
@JoinColumn(name="project_id", referencedColumnName="project_id", insertable=false, updatable=false),
@JoinColumn(name="dept_id", referencedColumnName="dept_id", insertable=false, updatable=false)
)
Project relatedProject;
.....
.....
}
****the entity Project is consist of data from 2 tables: "project" and "project_detail1"
****the primary key is (dept_id, project_id)
***the entity AuditLog is consist of data from one table only: audit_log
***AuditLog.relatedProject point to the Project, being log, by joining the dept_id and ***project_id
now if i issue a left join query as the following:
select al from AuditLog al left join al.relatedProject
toplink generate incorrect SQL:
select t0.log_id, t0.project_id, t0.dept_id, t1.dept_id, t2.name
from audit_log t0 left outer join
(project t1 join project_detail1 t2 on [b](t1.dept_id = t2.dept_id)[/b])
on ((t0.project_id = t1.project_id) and (t0.dept_id = t1.dept_id))
>>>>>problem --- joining table project and project_id should use 2 columns (dept_id, project_id)
>>>>>but the SQL generated use only one, not the complete composite primary key !!
the SQL should be:
select t0.log_id, t0.project_id, t0.dept_id, t1.dept_id, t2.name
from audit_log t0 left outer join
(project t1 join project_detail1 t2 on [b]((t1.dept_id = t2.dept_id) and (t1.project_id = t2.project_id))[/b]) /* join using 2 columns*/
on ((t0.project_id = t1.project_id) and (t0.dept_id = t1.dept_id))
is this a know bug ? (if yes, any workaround)
or
somebody know if there is something wrong in entity setting / configuration?
thank you!!
[Message sent by forum member 'imperfect' (imperfect)]
http://forums.java.net/jive/thread.jspa?messageID=209136