My Oracle Support Banner

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

Last updated on MARCH 26, 2019

Applies to:

Oracle TopLink - Version 11.1.1.4.0 to 11.1.1.5.0 [Release Oracle11g]
Information in this document applies to any platform.

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

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


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