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

Bug 319266

Summary: Creation of temporary tables for MySQL database update all query
Product: z_Archived Reporter: Ravi kiran N <ravikirann>
Component: EclipselinkAssignee: Nobody - feel free to take it <nobody>
Status: RESOLVED INVALID QA Contact:
Severity: normal    
Priority: P3 CC: ravikirann, tom.ware
Version: unspecified   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   
Whiteboard:
Attachments:
Description Flags
MySQL temporary table test case. none

Description Ravi kiran N CLA 2010-07-08 09:40:53 EDT 
Build Identifier: 1.1.1

When we try to use JPQL update all query in a one to many bidirectional relationship, we see temporary table being created and deleted and some inserts happening with specifically for MySQL database, whereas it seems to work fine with other databases with a single query. 
Is is it possible to avoid such creation of temporary tables and with a single sql query using JPQL. Creation of a temporary table is scalability issue.




We get the queries something like this when we use MySQL db,

CREATE TEMPORARY TABLE IF NOT EXISTS TL_orders_bidirectional LIKE orders_bidirectional
INSERT INTO TL_orders_bidirectional (ID, NAME) SELECT t1.ID, ? FROM customer_bidirectional t0, orders_bidirectional t1 WHERE ((t0.ID = ?) AND (t0.ID = t1.CUSTOMER_ID))
	bind => [9, 1]
UPDATE orders_bidirectional, TL_orders_bidirectional SET orders_bidirectional.NAME = TL_orders_bidirectional.NAME WHERE orders_bidirectional.ID = TL_orders_bidirectional.ID
DELETE FROM TL_orders_bidirectional


Instead why can't we generate something like this,

UPDATE orders_bidirectional o ,customer_bidirectional  c set o.Name='12' where c.ID='1' and c.id = o.customer_id;

Reproducible: Always

Steps to Reproduce:
1.Create a one to many bidirectional relationship.
2.Fire an update all JPQL query on MySQL database
3.Test case is attached
Comment 1 Ravi kiran N CLA 2010-07-08 09:42:24 EDT 
Created attachment 173770 [details]
MySQL temporary table test case.
Comment 2 Ravi kiran N CLA 2010-07-20 06:05:14 EDT 
Any updates regarding this issue, we have an urgent requirement in our application. Is there a way to avoid creation of temporary tables and use update all JPQL query.
Comment 3 Tom Ware CLA 2010-08-04 11:52:04 EDT 
Certain types of update and delete all statements (for instance for Entities mapped to multiple tables) require SQL that is more complex than a standard Update or Delete SQL statement - since multiple tables have to be somehow referenced and Update and Delete SQL statements only allow one table.

We address this issue in one of two ways.

1. We use a Subquery in the Update or Delete statement.  We do this for all databases that allowed the subquery at the time we wrote the feature.

2. If Subqueries are not allowed in Update or Delete statements, but temporary tables are allowed, we use temporary tables.  That is what is happening with MySQL.

In your example the reason multiple tables are needed is because your where clause navigates from Order to Customer.

I am closing this bug as Invalid, but feel free to reopen if you can suggest a single line Update statement that works on MySQL.
Comment 4 Ravi kiran N CLA 2010-08-06 02:42:03 EDT 
As already mentioned why can't we generate a sql query something like below, instead of using temporary tables. This works fine on MySQL platform. Using temporary tables is not an option as it will lead to performance issues.

UPDATE CUSTOMER_BIDIRECTIONAL t0, ORDERS_BIDIRECTIONAL t1 SET t1.NAME='3' WHERE ((t0.ID = '1') AND (t0.ID = t1.CUSTOMER_ID))
Comment 5 Eclipse Webmaster CLA 2022-06-09 10:16:24 EDT 
The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink
Comment 6 Eclipse Webmaster CLA 2022-06-09 10:25:25 EDT 
The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink