Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Bug in NOT MEMBER OF translation?(JPQL)
Bug in NOT MEMBER OF translation? [message #520957] Mon, 15 March 2010 22:58 Go to next message
Daniel Le Berre is currently offline Daniel Le BerreFriend
Messages: 82
Registered: July 2009
Member
Coming back to my earlier example :
"SELECT interest, COUNT(contact) AS nbContacts " +
				"FROM SocialEntity soc LEFT JOIN FETCH soc.interests, IN(soc.contacts) contact, " +
				"IN(contact.interests) interest " +
				"WHERE soc = :socialEntity AND interest NOT MEMBER OF soc.interests " +
				"GROUP BY interest ORDER BY nbContacts DESC"


The request does not work as expected because it works only when soc.interest is empty.

That JPQL request is translated into the following SQL statement:
SELECT interest.ID, interest.NAME, interest.PARENTINTEREST_ID, COUNT(contact.ID) 
FROM SOCIALENTITY_INTEREST t4, SOCIAL_ENTITY__CONTACTS t3, SOCIALENTITY soc, SOCIALENTITY contact, INTEREST interest 
WHERE (((? = soc.ID) AND NOT EXISTS 
     (SELECT DISTINCT t6.ID FROM SOCIALENTITY_INTEREST t5, INTEREST t6 
      WHERE (((t5.entities_ID = soc.ID) AND (t6.ID = t5.interests_ID)) 
         AND ((t4.entities_ID = contact.ID) AND (interest.ID = t4.interests_ID)))) ) 

     AND (((t3.SocialEntity_ID = soc.ID) AND (contact.ID = t3.contacts_ID)) 
     AND ((t4.entities_ID = contact.ID) AND (interest.ID = t4.interests_ID)))) 
GROUP BY interest.ID, interest.NAME, interest.PARENTINTEREST_ID ORDER BY COUNT(contact.ID) DESC


One can see that the where clause of the subquery does not seem correct.

One would expect something like
WHERE (((t5.entities_ID = soc.ID) AND (t6.ID = t5.interests_ID)) 
         AND ((t4.entities_ID = contact.ID) AND (interest.ID = t4.interests_ID)
         AND (interest.ID = t6.ID)))) ) 


(the content of tables 5 and 6 should be restricted by the interest object, which is not the case currently)

Am I correct or did I miss something?

[Updated on: Tue, 16 March 2010 05:36]

Report message to a moderator

Re: Bug in NOT MEMBER OF translation? [message #522436 is a reply to message #520957] Mon, 22 March 2010 16:07 Go to previous messageGo to next message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

Seems odd, I don't see why the EXISTS is re-joining the interest table at all, or why it is join with contact as well? How do the classes map to the tables?

EclipseLink always use t# in its generated SQL, but you have other aliases? Did you edit the generated SQL?

Since you are selecting interest, not soc, why are you join fetching soc?

You could just try a JOIN instead of the MEMBER OF,


"SELECT interest, COUNT(contact) AS nbContacts " +
"FROM SocialEntity soc JOIN soc.contacts contact, " +
" JOIN contact.interests interest " +
" JOIN contact.interests socInterest " +
"WHERE soc = :socialEntity AND interest <> socInterests " +
"GROUP BY interest ORDER BY nbContacts DESC"


James : Wiki : Book : Blog : Twitter
Re: Bug in NOT MEMBER OF translation? [message #522495 is a reply to message #522436] Mon, 22 March 2010 19:18 Go to previous messageGo to next message
Daniel Le Berre is currently offline Daniel Le BerreFriend
Messages: 82
Registered: July 2009
Member
Quote:

Seems odd, I don't see why the EXISTS is re-joining the interest table at all, or why it is join with contact as well? How do the classes map to the tables?



A social entity has a many to many relationship with interest.
A social entity has a many to many relationship with itself (contact)

All the details are available from http://fsnet.googlecode.com

Quote:

EclipseLink always use t# in its generated SQL, but you have other aliases? Did you edit the generated SQL?



yes, I changed the t# by more meaningful names for me. Else no way to understand what is going on Smile

Quote:

Since you are selecting interest, not soc, why are you join fetching soc?



I thought first that the not member of was not working because of the lazy fetching on interests.

The FETCH JOIN is useless here, you are right.

Quote:

You could just try a JOIN instead of the MEMBER OF, ...



I have to check if that query really means what I want: "give me the interests of my contacts that I do not have yet".
Re: Bug in NOT MEMBER OF translation? [message #522519 is a reply to message #522436] Mon, 22 March 2010 21:27 Go to previous messageGo to next message
Daniel Le Berre is currently offline Daniel Le BerreFriend
Messages: 82
Registered: July 2009
Member
Quote:

"SELECT interest, COUNT(contact) AS nbContacts " +
"FROM SocialEntity soc JOIN soc.contacts contact, " +
" JOIN contact.interests interest " +
" JOIN contact.interests socInterest " +
"WHERE soc = :socialEntity AND interest <> socInterests " +
"GROUP BY interest ORDER BY nbContacts DESC"



That request will return all the social entity contact's interests provided the social entity has more than one interest.

I do not think it is possible to implement the NOT MEMBER OF without subqueries.
Re: Bug in NOT MEMBER OF translation? [message #523202 is a reply to message #520957] Thu, 25 March 2010 14:11 Go to previous messageGo to next message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

I'm having trouble understanding the query. Could you remove the fetch join and the group/order by and simplify the query to just the member of. Also include the classes with their relationships and tables.

You could also use your own sub-select in the JPA instead of the member of, this might give you more control and understanding of the query.

The above query with an outer join may also work.


James : Wiki : Book : Blog : Twitter
Re: Bug in NOT MEMBER OF translation? [message #523237 is a reply to message #523202] Thu, 25 March 2010 15:51 Go to previous messageGo to next message
Daniel Le Berre is currently offline Daniel Le BerreFriend
Messages: 82
Registered: July 2009
Member
Quote:

@Entity
public class SocialEntity implements Serializable {

@ManyToMany(cascade = {CascadeType.PERSIST})
@OrderBy(value="name")
private List<Interest> interests = new ArrayList<Interest>();

@OneToMany
@JoinTable(name = "SOCIAL_ENTITY__CONTACTS")
private List<SocialEntity> contacts;
...
}



What I would like to know is "which are the interests owned by my contacts that I do not own myself ?".

It should be ok to write something like:
SELECT interest FROM SocialEntity soc ,IN(soc.contacts) contact, IN(contact.interests) interest
WHERE soc = :socialEntity AND interest NOT MEMBER OF soc.interests
Re: Bug in NOT MEMBER OF translation? [message #523296 is a reply to message #520957] Thu, 25 March 2010 19:26 Go to previous messageGo to next message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

Could you include the SQL the simplified query generates, and the SQL that you desire.

I think just doing an outerjoin to the soc interest and comparing them would also work, and not require a sub-select.


James : Wiki : Book : Blog : Twitter
Re: Bug in NOT MEMBER OF translation? [message #523321 is a reply to message #523296] Thu, 25 March 2010 21:35 Go to previous messageGo to next message
Daniel Le Berre is currently offline Daniel Le BerreFriend
Messages: 82
Registered: July 2009
Member
JPQL QUERY

        List<Interest> whole = em.createQuery(
                "SELECT interest "
                + "FROM SocialEntity soc, IN(soc.contacts) contact, "
                + "IN(contact.interests) interest "
                + "WHERE soc = :socialEntity AND interest NOT MEMBER OF soc.interests ", Interest.class).setParameter("socialEntity", socialEntity).getResultList();


GENERATED SQL
SELECT t0.ID, t0.NAME, t0.PARENTINTEREST_ID FROM SOCIALENTITY_INTEREST t4, SOCIAL_ENTITY__CONTACTS t3, SOCIALENTITY t2, SOCIALENTITY t1, INTEREST t0 
WHERE (((? = t2.ID) AND NOT EXISTS (SELECT DISTINCT t6.ID FROM SOCIALENTITY_INTEREST t5, INTEREST t6 WHERE (((t5.entities_ID = t2.ID) AND (t6.ID = t5.interests_ID)) AND ((t4.entities_ID = t1.ID) AND (t0.ID = t4.interests_ID)))) ) AND (((t3.SocialEntity_ID = t2.ID) AND (t1.ID = t3.contacts_ID)) AND ((t4.entities_ID = t1.ID) AND (t0.ID = t4.interests_ID))))

Re: Bug in NOT MEMBER OF translation? [message #523825 is a reply to message #520957] Mon, 29 March 2010 14:12 Go to previous messageGo to next message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

The SQL does seem odd, please log a bug for this. Using your own sub-select, or an outerjoin should be able to workaround the issue, otherwise use native SQL.

I would expect the SQL to be something like:

SELECT interest
FROM SOCIALENTITY_INTEREST contacts.interests, SOCIAL_ENTITY__CONTACTS soc.contacts, SOCIALENTITY contact*, SOCIALENTITY soc, INTEREST interest

WHERE (((? = soc.ID) AND NOT EXISTS (

SELECT DISTINCT soc.interest FROM SOCIALENTITY_INTEREST soc.interests, INTEREST soc.interest
WHERE (((soc.interests.entities_ID = soc.ID) AND (soc.interest.ID = soc.interests.interests_ID))

// This part seems wrong "AND ((contacts.interests.entities_ID = soc.ID) AND (interest.ID = contacts.interests.interests_ID)))) ) "
-> (interest.ID = soc.interest.ID)

AND (((soc.contacts.SocialEntity_ID = soc.ID) AND (contact.ID = soc.contacts.contacts_ID))
AND ((contacts.interests.entities_ID = contact.ID) AND (interest.ID = contacts.interests.interests_ID))))


James : Wiki : Book : Blog : Twitter
Re: Bug in NOT MEMBER OF translation? [message #523848 is a reply to message #523825] Mon, 29 March 2010 15:37 Go to previous message
Daniel Le Berre is currently offline Daniel Le BerreFriend
Messages: 82
Registered: July 2009
Member
Done.

See bug 307412.
Previous Topic:entitymanager.close() doesn't destroy persistence context in SE application
Next Topic:Initialize EclipseLink at runtime
Goto Forum:
  


Current Time: Sat Dec 20 18:43:24 GMT 2014

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

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