You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by "Rob Wisniewski (JIRA)" <ji...@apache.org> on 2007/07/24 22:29:31 UTC
[jira] Created: (OPENJPA-292) Extra JOIN on eager bi-directional
relationship
Extra JOIN on eager bi-directional relationship
-----------------------------------------------
Key: OPENJPA-292
URL: https://issues.apache.org/jira/browse/OPENJPA-292
Project: OpenJPA
Issue Type: Bug
Affects Versions: 1.0.0
Reporter: Rob Wisniewski
I have a pretty simple 1-1 bi-directional relationship. If I set both sides to eager and then do a select on one side, the following SQL is executed:
SELECT t1.USERID, t2.ACCOUNTID, t2.BALANCE
, t2.CREATIONDATE, t2.LASTLOGIN, t2.LOGINCOUNT, t2.LOGOUTCOUNT, t2.OPENBALANCE,
t1.ADDRESS, t1.CREDITCARD, t1.EMAIL, t1.FULLNAME, t1.PASSWD FROM ACCOUNTEJB t0 I
NNER JOIN ACCOUNTPROFILEEJB t1 ON t0.PROFILE_USERID = t1.USERID LEFT OUTER JOIN
ACCOUNTEJB t2 ON t1.USERID = t2.PROFILE_USERID WHERE t0.ACCOUNTID = ? optimize
for 1 row
the relationship is account <-> accountprofile. you can see we actually do 2 joins.
This is one in a family of problems which was supposed to have been solved in https://issues.apache.org/jira/browse/OPENJPA-134
There is also a related issue where an uneeded load is done, which I documented a while ago in https://issues.apache.org/jira/browse/OPENJPA-241
I think this is a general comment on the lack of sophistication of the persistence engine to understand when data is logically going to be there anyways, and not to add joins or trigger data loading.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (OPENJPA-292) Extra JOIN on eager bi-directional
relationship
Posted by "Fay Wang (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-292?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Fay Wang updated OPENJPA-292:
-----------------------------
Attachment: openjpa_292_1.patch
The attached patch address the issue raised by Catalina:
if simply return for decMeta == null, the following code execution will be skipped:
sm.storeObject(field.getIndex(), res.load(cls, store, fetch,
eagerJoin(res.newJoins(), cls, false)));
> Extra JOIN on eager bi-directional relationship
> -----------------------------------------------
>
> Key: OPENJPA-292
> URL: https://issues.apache.org/jira/browse/OPENJPA-292
> Project: OpenJPA
> Issue Type: Bug
> Affects Versions: 1.0.0
> Reporter: Rob Wisniewski
> Attachments: openjpa_292.patch, openjpa_292_1.patch
>
>
> I have a pretty simple 1-1 bi-directional relationship. If I set both sides to eager and then do a select on one side, the following SQL is executed:
> SELECT t1.USERID, t2.ACCOUNTID, t2.BALANCE
> , t2.CREATIONDATE, t2.LASTLOGIN, t2.LOGINCOUNT, t2.LOGOUTCOUNT, t2.OPENBALANCE,
> t1.ADDRESS, t1.CREDITCARD, t1.EMAIL, t1.FULLNAME, t1.PASSWD FROM ACCOUNTEJB t0 I
> NNER JOIN ACCOUNTPROFILEEJB t1 ON t0.PROFILE_USERID = t1.USERID LEFT OUTER JOIN
> ACCOUNTEJB t2 ON t1.USERID = t2.PROFILE_USERID WHERE t0.ACCOUNTID = ? optimize
> for 1 row
> the relationship is account <-> accountprofile. you can see we actually do 2 joins.
> This is one in a family of problems which was supposed to have been solved in https://issues.apache.org/jira/browse/OPENJPA-134
> There is also a related issue where an uneeded load is done, which I documented a while ago in https://issues.apache.org/jira/browse/OPENJPA-241
> I think this is a general comment on the lack of sophistication of the persistence engine to understand when data is logically going to be there anyways, and not to add joins or trigger data loading.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (OPENJPA-292) Extra JOIN on eager bi-directional
relationship
Posted by "Fay Wang (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-292?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Fay Wang updated OPENJPA-292:
-----------------------------
Attachment: testcase_241_292.patch
The attached test cases are for JIRA-241 and JIRA292.
> Extra JOIN on eager bi-directional relationship
> -----------------------------------------------
>
> Key: OPENJPA-292
> URL: https://issues.apache.org/jira/browse/OPENJPA-292
> Project: OpenJPA
> Issue Type: Bug
> Affects Versions: 1.0.0
> Reporter: Rob Wisniewski
> Attachments: openjpa_292.patch, openjpa_292_1.patch, testcase_241_292.patch
>
>
> I have a pretty simple 1-1 bi-directional relationship. If I set both sides to eager and then do a select on one side, the following SQL is executed:
> SELECT t1.USERID, t2.ACCOUNTID, t2.BALANCE
> , t2.CREATIONDATE, t2.LASTLOGIN, t2.LOGINCOUNT, t2.LOGOUTCOUNT, t2.OPENBALANCE,
> t1.ADDRESS, t1.CREDITCARD, t1.EMAIL, t1.FULLNAME, t1.PASSWD FROM ACCOUNTEJB t0 I
> NNER JOIN ACCOUNTPROFILEEJB t1 ON t0.PROFILE_USERID = t1.USERID LEFT OUTER JOIN
> ACCOUNTEJB t2 ON t1.USERID = t2.PROFILE_USERID WHERE t0.ACCOUNTID = ? optimize
> for 1 row
> the relationship is account <-> accountprofile. you can see we actually do 2 joins.
> This is one in a family of problems which was supposed to have been solved in https://issues.apache.org/jira/browse/OPENJPA-134
> There is also a related issue where an uneeded load is done, which I documented a while ago in https://issues.apache.org/jira/browse/OPENJPA-241
> I think this is a general comment on the lack of sophistication of the persistence engine to understand when data is logically going to be there anyways, and not to add joins or trigger data loading.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (OPENJPA-292) Extra JOIN on eager bi-directional
relationship
Posted by "Fay Wang (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-292?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Fay Wang updated OPENJPA-292:
-----------------------------
Attachment: openjpa_292.patch
The problem described by Rob is similar to JIRA-241 and JIRA-134 scenario 3 in that they are all about the retrieval of the inverse relationship. This JIRA deals with 1-1 relationship while JIRA-241 is for 1-many relationship. The following test case is used to reproduce the problem:
@Entity
public class EntityC {
@GeneratedValue(strategy=GenerationType.IDENTITY)
@Id private int id;
private String name;
private int age;
private int balance;
@OneToOne(fetch=FetchType.EAGER, mappedBy="entityC")
private EntityD entityD = null;
...
}
@Entity
public class EntityD {
@GeneratedValue(strategy=GenerationType.IDENTITY)
@Id private int id;
private String name;
private int loginCount;
private int logoutCount;
private String email;
@OneToOne(fetch=FetchType.EAGER)
private EntityC entityC = null;
...
}
The test case:
EntityManagerImpl em = (EntityManagerImpl) emf.createEntityManager();
String query = "select c FROM EntityC c";
Query q = em.createQuery(query);
List list = q.getResultList();
System.out.println("size = " + list.size());
for (int i = 0; i < list.size(); i++) {
EntityC c = (EntityC)list.get(i);
System.out.println("EntityC = " + c.getName());
System.out.println("EntityD = " + c.getD().getName());
System.out.println("EntityD's C = " + c.getD().getC().getName());
System.out.println();
}
The following SQL are generated:
(1) SELECT t0.id, t0.age, t0.balance, t1.id, t1.email, t1.loginCount, t1.logoutCount, t1.name, t0.name
FROM EntityC t0
LEFT OUTER JOIN EntityD t1 ON t0.id = t1.ENTITYC_ID
(2) SELECT t1.id, t1.age, t1.balance, t2.id, t2.email, t2.loginCount, t2.logoutCount, t2.name, t1.name
FROM EntityD t0
INNER JOIN EntityC t1 ON t0.ENTITYC_ID = t1.id
LEFT OUTER JOIN EntityD t2 ON t1.id = t2.ENTITYC_ID WHERE t0.id = ? optimize for 1 row [params=(int) 101]
(3) SELECT t1.id, t1.age, t1.balance, t2.id, t2.email, t2.loginCount, t2.logoutCount, t2.name, t1.name
FROM EntityD t0
INNER JOIN EntityC t1 ON t0.ENTITYC_ID = t1.id
LEFT OUTER JOIN EntityD t2 ON t1.id = t2.ENTITYC_ID WHERE t0.id = ? optimize for 1 row [params=(int) 102]
(4) SELECT t1.id, t1.age, t1.balance, t2.id, t2.email, t2.loginCount, t2.logoutCount, t2.name, t1.name
FROM EntityD t0
INNER JOIN EntityC t1 ON t0.ENTITYC_ID = t1.id
LEFT OUTER JOIN EntityD t2 ON t1.id = t2.ENTITYC_ID WHERE t0.id = ? optimize for 1 row [params=(int) 104]
(5) SELECT t1.id, t1.age, t1.balance, t2.id, t2.email, t2.loginCount, t2.logoutCount, t2.name, t1.name
FROM EntityD t0
INNER JOIN EntityC t1 ON t0.ENTITYC_ID = t1.id
LEFT OUTER JOIN EntityD t2 ON t1.id = t2.ENTITYC_ID WHERE t0.id = ? optimize for 1 row [params=(int) 103]
Since there are four entityD in the database, openjpa makes four separate SQL calls to retrieve each entityD (sql (2) - (5)). These sql has inner join and left outer join between EntityC and EntityD as they are bi-directional 1-1 relationship.
The attached patch detects this inverse relationship (mappedBy relationship) to get rid of sql (2) -(5).
> Extra JOIN on eager bi-directional relationship
> -----------------------------------------------
>
> Key: OPENJPA-292
> URL: https://issues.apache.org/jira/browse/OPENJPA-292
> Project: OpenJPA
> Issue Type: Bug
> Affects Versions: 1.0.0
> Reporter: Rob Wisniewski
> Attachments: openjpa_292.patch
>
>
> I have a pretty simple 1-1 bi-directional relationship. If I set both sides to eager and then do a select on one side, the following SQL is executed:
> SELECT t1.USERID, t2.ACCOUNTID, t2.BALANCE
> , t2.CREATIONDATE, t2.LASTLOGIN, t2.LOGINCOUNT, t2.LOGOUTCOUNT, t2.OPENBALANCE,
> t1.ADDRESS, t1.CREDITCARD, t1.EMAIL, t1.FULLNAME, t1.PASSWD FROM ACCOUNTEJB t0 I
> NNER JOIN ACCOUNTPROFILEEJB t1 ON t0.PROFILE_USERID = t1.USERID LEFT OUTER JOIN
> ACCOUNTEJB t2 ON t1.USERID = t2.PROFILE_USERID WHERE t0.ACCOUNTID = ? optimize
> for 1 row
> the relationship is account <-> accountprofile. you can see we actually do 2 joins.
> This is one in a family of problems which was supposed to have been solved in https://issues.apache.org/jira/browse/OPENJPA-134
> There is also a related issue where an uneeded load is done, which I documented a while ago in https://issues.apache.org/jira/browse/OPENJPA-241
> I think this is a general comment on the lack of sophistication of the persistence engine to understand when data is logically going to be there anyways, and not to add joins or trigger data loading.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.