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

Last updated on SEPTEMBER 14, 2012

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 fcil From GSP_Facility fcil WHERE EXISTS( SELECT c FROM GSP_GeographicCode c WHERE c = fcil.codes and c.code =:code)

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

Translated SQL:

SELECT t0.GEO_LOCATION_ID, t0.DTYPE, t0.LONGITUDE, t0.LATITUDE, t0.JPA_VERSION, t0.FCIL_VSL_ENTRY_HRS, t0.FCIL_VSL_EXIT_HRS
FROM TLG_DE1001_LOCATION t0
WHERE
(EXISTS
(SELECT 1
FROM TLG_DE1001_LOCATION t2, TLG_DE1001_LOCATION_CODE t1
WHERE ((((t1.GEO_LOCATION_ID = t2.GEO_LOCATION_ID)
AND (t1.CODE = 'HKG01'))
AND (t0.GEO_LOCATION_ID = t2.GEO_LOCATION_ID))
AND (t2.DTYPE = 'Facility')))
AND (t0.DTYPE = 'Facility'));

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

And expected SQL is:

SELECT t0.GEO_LOCATION_ID, t0.DTYPE, t0.LONGITUDE, t0.LATITUDE, t0.JPA_VERSION, t0.FCIL_VSL_ENTRY_HRS, t0.FCIL_VSL_EXIT_HRS
FROM TLG_DE1001_LOCATION t0
WHERE
(EXISTS
(SELECT 1
FROM TLG_DE1001_LOCATION_CODE t1
WHERE ((t1.GEO_LOCATION_ID = t0.GEO_LOCATION_ID)
AND (t1.CODE = 'HKG01')))
AND (t0.DTYPE = 'Facility'));

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

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms