Skip to main content
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
Re: [eclipselink-dev] same table in from clauses of query and subquery 
Hi Dies,
I don't think it would be easy to make Eclipselink to substitute WHERE EXISTS with something else 
(and it may not be possible to use your solution in case of a composite pk).
If the db supports temporary tables the quick fix is to define on your platform 
public boolean shouldAlwaysUseTempStorageForModifyAll() {

 return true;

}
This forces usage of temporary tables each time more than one table envolved in UpdateAll/DeleteAll That would require defining other temporary table - related methods as well (see for example DB2Platform, SybasePlatform, MySQLPlatform). 

Currently all db platforms supported by Eclipselink use WHERE EXISTS,
except MySQL (version 4 didn't support it, but I believe version 5 does).

Thanks,
Andrei
----- Original Message ----- From: "Dies Koper" <diesk@xxxxxxxxxxxxxxxxxxx> To: "Dev mailing list for Eclipse Persistence Services" <eclipselink-dev@xxxxxxxxxxx> 
Sent: Tuesday, September 15, 2009 10:23 AM
Subject: [eclipselink-dev] same table in from clauses of query and subquery



Hi Tom,

I have identified another issue while running JUnit tests on Symfoware.
I was wondering whether you have come across this issue before for any
of the other databases, and know an easy way to resolved it.

The JUnit test tries to invoke the following JPQL statement:

   [junit] Query: DeleteAllQuery(referenceClass=Project sql="DELETE
FROM CMP3_PROJECT WHERE (PROJ_NAME = 'testUpdateAllProjects')")

This is converted to the following SQL statement:

   [junit] Call: DELETE FROM CMP3_LPROJECT WHERE EXISTS(SELECT
t0.PROJ_ID FROM CMP3_PROJECT t0, CMP3_LPROJECT t1 WHERE ((t0.PROJ_NAME =
'testUpdateAllProjects') AND ((t1.PROJ_ID = t0.PROJ_ID) AND
(t0.PROJ_TYPE = 'L'))) AND t1.PROJ_ID = CMP3_LPROJECT.PROJ_ID)

Symfoware complains that the FROM clause in the DELETE part contains the
same table or view ("CMP3_LPROJECT") as the FROM clause in the sub-query.

The following is the same query but with improved readability:

DELETE FROM CMP3_LPROJECT
 WHERE EXISTS(
   SELECT t0.PROJ_ID
   FROM CMP3_PROJECT t0, CMP3_LPROJECT t1
   WHERE (
           (t0.PROJ_NAME = 'testUpdateAllProjects') AND
           (
             (t1.PROJ_ID = t0.PROJ_ID) AND (t0.PROJ_TYPE = 'L')
           )
         ) AND
         t1.PROJ_ID = CMP3_LPROJECT.PROJ_ID
   )

I think this query is equivalent to the following one, which does not
give an error on Symfoware:

DELETE FROM CMP3_LPROJECT
 WHERE PROJ_ID = (
   SELECT t0.PROJ_ID
   FROM CMP3_PROJECT t0
   WHERE (
           (t0.PROJ_NAME = 'testUpdateAllProjects') AND
           (t0.PROJ_TYPE = 'L')
         )
 )

I have also seen the Symfoware error message for similar UPDATE queries.
I think the code that generated these statements are in
SQLDeleteAllStatement#buildCall and SQLUpdateAllStatement#buildCall.
There already is a code path to "extract its where clause", which I
suppose I did above, but this case does not satisfy its conditions.

I thought I'd bring it up just in case you know of a simple flag to make
it work before I try to reinvent the wheel.

Thanks!
Dies


_______________________________________________
eclipselink-dev mailing list
eclipselink-dev@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-dev

Back to the top