Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Left join with where condition
Left join with where condition [message #1413080] Wed, 27 August 2014 18:38 Go to next message
Gowthami Shanmugam is currently offline Gowthami ShanmugamFriend
Messages: 4
Registered: August 2014
Junior Member
Hi,

I'm trying to use left join as below in eclipse link 2.4.

Query:

select mt.id from MemberTransaction as mt left join mt.pointsTransaction as ptr where ptr.memberTransaction.id IS NULL


and the generated SQL query is:

SELECT * FROM (SELECT /*+ FIRST_ROWS */ a.*, ROWNUM rnum FROM (
SELECT t0.MEMBER_TRANSACTION_ID AS a1
FROM RS.MEMBER_TRANSACTION t0
LEFT OUTER JOIN RS.POINTS_TRANSACTION t1 ON (t1.MEMBER_TRANSACTION_ID = t0.MEMBER_TRANSACTION_ID),
RS.MEMBER_TRANSACTION t2
WHERE ((t2.MEMBER_TRANSACTION_ID IS NULL) AND (t2.MEMBER_TRANSACTION_ID = t1.MEMBER_TRANSACTION_ID))) a WHERE ROWNUM <= ?) WHERE rnum > ?

If you look into the query in where condition "t2.MEMBER_TRANSACTION_ID = t1.MEMBER_TRANSACTION_ID" was added but it was not in my actual query.

When i did research and found that "ptr.memberTransaction.id IS NULL" will lead to add condition "t2.MEMBER_TRANSACTION_ID = t1.MEMBER_TRANSACTION_ID".

I'm not sure why eclipse link is behaving different. i tried in hibernate and got the actual result without any additional condition.

Is it a bug or the expected behavior.
Re: Left join with where condition [message #1413852 is a reply to message #1413080] Fri, 29 August 2014 15:23 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1089
Registered: July 2009
Senior Member
It looks like it is coming from the "where ptr.memberTransaction.id IS NULL" clause, and should be expected. The ptr.memberTransaction is making an inner join from POINTS_TRANSACTION to the memberTransaction relationship (MEMBER_TRANSACTION I assume) to check that the ID is null. You might try using this instead:
"where ptr.memberTransaction IS NULL"

Other providers may inspect your ptr.memberTransaction.id and interpret that you don't want the join, but it seems to go against the language and nature of the JPA specification - there can never be a memberTransaction with a null ID. Even using ptr.memberTransaction IS NULL may not work for every type of relation, such as when the target contains the foreign key, and so may force the use of an inner join. In those cases, you would need to specify that an outer join be used explicietly, the same way you are for the mt.pointsTransaction relationship.
Re: Left join with where condition [message #1413880 is a reply to message #1413852] Fri, 29 August 2014 16:52 Go to previous message
Gowthami Shanmugam is currently offline Gowthami ShanmugamFriend
Messages: 4
Registered: August 2014
Junior Member
I want to be clear,

Actual SQL Query:
select mt.member_transaction_id from Member_Transaction mt left outer join Points_Transaction ptr on mt.member_Transaction_Id = ptr.member_Transaction_Id where (ptr.member_Transaction_Id IS NULL)

JPQL Query:

String sql = "select mt.id from MemberTransaction as mt left join mt.pointsTransaction as ptr where ptr.memberTransaction.id IS NULL";

here in member transaction Entity, Member_Transation_Id was replaced as Id.

Eclipse Link generated SQL:
SELECT * FROM (SELECT a.*, ROWNUM rnum FROM (SELECT t0.MEMBER_TRANSACTION_ID AS a1 FROM RS.MEMBER_TRANSACTION t0 LEFT OUTER JOIN RS.POINTS_TRANSACTION t1 ON (t1.MEMBER_TRANSACTION_ID = t0.MEMBER_TRANSACTION_ID),RS.MEMBER_TRANSACTION t2 WHERE ((t2.MEMBER_TRANSACTION_ID IS NULL) AND (t2.MEMBER_TRANSACTION_ID = t1.MEMBER_TRANSACTION_ID))) a WHERE ROWNUM <= ?) WHERE rnum > ?


It creates a new alies for MEMBER_TRANSACTION as t2.

In the actual query ptr.member_Transaction_Id IS NULL which means checking for Point_Transaction.member_Transaction_Id IS NULL

But the generated SQL check against Member_Transaction.member_Transaction_Id IS NULL
and one more condition in excess t2.MEMBER_TRANSACTION_ID = t1.MEMBER_TRANSACTION_ID

which will lead to different result set.

I'm not sure why the entire query got changed
Previous Topic:Migration Glassfish 3.1.2 to Wildfly: Challenge trying to get EclipseLink to Work
Next Topic:Using Minus in JPQL
Goto Forum:
  


Current Time: Sat Sep 05 04:00:35 GMT 2015

Powered by FUDForum. Page generated in 0.01985 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software