Eclipse Community Forums: EclipseLink » Left join with where condition

Home » Eclipse Projects » EclipseLink » Left join with where condition

Left join with where condition [message #1413080]

Wed, 27 August 2014 14:38

Eclipse User

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 11:23

Eclipse User

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 12:52

Eclipse User

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:

-=] Back to Top [=-

Current Time: Sun Jul 20 20:33:36 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter