JPQL Join Tables With One-to-many Relationship Creates Duplicate Joined Table (Doc ID 1490370.1)

Last updated on OCTOBER 18, 2021

Applies to:

Symptoms

You are using Toplink 11.1.1.4.0/EclipseLink 2.1.2 with JPA 2.0.

You are met with duplicated tables in translated SQL while using JQPL to join two tables which with one-to-many relationship. In the following example,the JPQL and its translated SQL are:

  JQPL: SELECT P From Project P WHERE EXISTS( SELECT c FROM Employee c WHERE c = P.codes and c.code =:code)

NOTE: entity 'Project' holds another entiry 'Employee' in a collection. (i.e one-to-many).

Translated SQL:

SELECT t0.Project, t0.DTYPE, t0.NAME, t0.LATITUDE, t0.JPA_VERSION, t0.StartTime, t0.Endtime
FROM Project t0
WHERE
(EXISTS
(SELECT 1
FROM Project t2, Project_CODE t1
WHERE ((((t1.Project = t2.Project)
AND (t1.CODE = '01'))
AND (t0.Project = t2.Project))
AND (t2.DTYPE = 'Largeproject)))
AND (t0.DTYPE = 'Largeproject));

You notice there were two 'Project' in above translated SQL.

And expected SQL is:

SELECT t0.Project, t0.DTYPE, t0.NAME, t0.LATITUDE, t0.JPA_VERSION, t0.StartTime, t0.Endtime
FROM Project t0
WHERE
(EXISTS
(SELECT 1
FROM Project_CODE t1
WHERE ((t1.Project = t0.Project)
AND (t1.CODE = '01')))
AND (t0.DTYPE = 'Largeproject));

You can see that 'Project' should only occur once. What is the solution to this issue?

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.