Re: [eclipselink-dev] same table in from clauses of query and subquery

[Dies Koper <diesk@xxxxxxxxxxxxxxxxxxx>]

Hi Andrei,

Thanks!
Symfoware does support global temporary tables. I have implemented the 
relevant methods and it looks like the queries that were causing 
problems have now been replaced by queries that use the temporary table.

I cannot quite yet pass these tests, there seems to be a locking issue, 
but it is looking promising!

Regards,
Dies


Andrei Ilitchev wrote:
> 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