Eclipse Community Forums: EclipseLink » Bug in NOT MEMBER OF translation?

Home

Home » Eclipse Projects » EclipseLink » Bug in NOT MEMBER OF translation?(JPQL)

Bug in NOT MEMBER OF translation? [message #520957]

Mon, 15 March 2010 18:58

Eclipse User

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 01:36] by Moderator

Re: Bug in NOT MEMBER OF translation? [message #522436 is a reply to message #520957]

Mon, 22 March 2010 12:07

Eclipse User

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"

Re: Bug in NOT MEMBER OF translation? [message #522495 is a reply to message #522436]

Mon, 22 March 2010 15:18

Eclipse User

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 17:27

Eclipse User

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

Eclipse User

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.

Re: Bug in NOT MEMBER OF translation? [message #523237 is a reply to message #523202]

Thu, 25 March 2010 11:51

Eclipse User

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 15:26

Eclipse User

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.

Re: Bug in NOT MEMBER OF translation? [message #523321 is a reply to message #523296]

Thu, 25 March 2010 17:35

Eclipse User

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

Eclipse User

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))))

Re: Bug in NOT MEMBER OF translation? [message #523848 is a reply to message #523825]

Mon, 29 March 2010 11:37

Eclipse User

Done.

See bug 307412.

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

Goto Forum:

-=] Back to Top [=-

Current Time: Tue Jul 22 11:51:23 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter