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

[Dies Koper <diesk@xxxxxxxxxxxxxxxxxxx>]

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