| Bug in NOT MEMBER OF translation? [message #520957] |
Mon, 15 March 2010 22:58  |
 Daniel Le Berre
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  |
|
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 #523202 is a reply to message #520957] |
Thu, 25 March 2010 14:11 |
|
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 #523321 is a reply to message #523296] |
Thu, 25 March 2010 21:35 |
Daniel Le Berre
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 |
|
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
|
|
|
|
Powered by
FUDForum. Page generated in 0.06104 seconds
] 
Search
Help
Register
Login
Home
