Some Eclipse Foundation services are deprecated, or will be soon. Please ensure you've read this important communication.

Bug 348134

Summary: DeleteAllQuery ignores additional join expression in multitable case
Product: z_Archived Reporter: Andrei Ilitchev <andrei.ilitchev>
Component: EclipselinkAssignee: Nobody - feel free to take it <nobody>
Status: CLOSED FIXED QA Contact:
Severity: normal    
Priority: P3    
Version: unspecified   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   
Whiteboard:
Attachments:
Description Flags
Suggested patch. none

Description Andrei Ilitchev CLA 2011-06-02 15:58:51 EDT 
The problem is reproduced on database platform that does not always use temporary storage (shouldAlwaysUseTempStorageForModifyAll() == false) for instance Oracle.

To reproduce run AdvancedMultiTenantJunitTest.testComplexMultitenantQueries test:
"DeleteAllMafiaFamilies" query produces last SQL wrong:

[EL Finest]: 2011-06-02 15:40:31.422--UnitOfWork(9930886)--Thread(Thread[Thread-11,6,main])--Execute query DeleteAllQuery(name="DeleteAllMafiaFamilies" referenceClass=MafiaFamily sql="DELETE FROM JPA_MAFIA_FAMILY WHERE NOT EXISTS(SELECT t0.ID FROM JPA_MAFIA_FAMILY t0, JPA_FAMILY_REVENUE t1 WHERE ((t1.ID = t0.ID) AND (t0.TENANT_ID = ?)) AND t0.ID = JPA_MAFIA_FAMILY.ID)")
[EL Fine]: 2011-06-02 15:40:31.422--ClientSession(26465608)--Connection(31520479)--Thread(Thread[Thread-11,6,main])--DELETE FROM JPA_FAMILY_TAGS WHERE EXISTS(SELECT t0.ID FROM JPA_MAFIA_FAMILY t0, JPA_FAMILY_REVENUE t1 WHERE ((t1.ID = t0.ID) AND (t0.TENANT_ID = ?)) AND t0.ID = JPA_FAMILY_TAGS.FAMILY_ID)
	bind => [123]
[EL Fine]: 2011-06-02 15:40:31.437--ClientSession(26465608)--Connection(31520479)--Thread(Thread[Thread-11,6,main])--DELETE FROM JPA_FAMILY_REVENUE WHERE EXISTS(SELECT t0.ID FROM JPA_MAFIA_FAMILY t0, JPA_FAMILY_REVENUE t1 WHERE ((t1.ID = t0.ID) AND (t0.TENANT_ID = ?)) AND t1.ID = JPA_FAMILY_REVENUE.ID)
	bind => [123]
[EL Fine]: 2011-06-02 15:40:31.437--ClientSession(26465608)--Connection(31520479)--Thread(Thread[Thread-11,6,main])--DELETE FROM JPA_MAFIA_FAMILY WHERE NOT EXISTS(SELECT t0.ID FROM JPA_MAFIA_FAMILY t0, JPA_FAMILY_REVENUE t1 WHERE ((t1.ID = t0.ID) AND (t0.TENANT_ID = ?)) AND t0.ID = JPA_MAFIA_FAMILY.ID)
	bind => [123]
...
junit.framework.AssertionFailedError: Incorrect number of families deleted [3], expected [1]

The correct sql won't have (t0.TENANT_ID = ?) in the subquery.
The logic as follows: 
the first sql deletes a mapping owned by the entity;
the second one deletes the data from the secondary table;
the third sql deletes the rows from the first table that are not joined to the secondary table.

Adding check for the tenant id there makes the third sql to delete all entries in the first table - no matter to which tenant they belong.

It's imperative that NOT EXISTS subquery does not have in the where clause nothing but a join between the first and the second tables.
Comment 1 Andrei Ilitchev CLA 2011-06-02 16:08:47 EDT 
Created attachment 197269 [details]
Suggested patch.

Fixed the problem by allowing DeleteAll query to substitute usage of 
  queryManager.getAdditionalJoinExpression();
for
  queryManager.getMultipleTableJoinExpression();
when NOT EXIST subquery is created.

Also added EntityManagerJUnitTestSuite.deleteAllProjects to confirm that the fix didn't break no where clause case when inheritance is involved.
Comment 2 Andrei Ilitchev CLA 2011-06-02 16:15:41 EDT 
The patch checked into trunk. Completed: At revision: 9501
Comment 3 Eclipse Webmaster CLA 2022-06-09 10:16:00 EDT 
The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink
Comment 4 Eclipse Webmaster CLA 2022-06-09 10:20:49 EDT 
The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink