cascading delete doing too much

Hi,

This is using LightSpeed 5.0 (it was not an issue in 4.0).

It happens when LightSpeed performs a cascading delete across at least two one-to-many associations, and the lowest level entity type is a base class of another entity type using class table inheritance. Deleting a top level entity will delete all entities at the lowest level, not just children of the top level entity.

I've attached an example project to demonstrate. The model contains entities:

• Country
• Town (FK to Country)
• Landmark (FK to Town)
• FancyLandmark (derived by class table inheritance from Landmark)

If I delete a country, then all Landmarks get deleted, not just those belonging to the country being deleted. It seems to be because of this LightSpeed-generated SQL:

DELETE FROM
  Landmark
WHERE
  EXISTS (
    SELECT
      Landmark.*
    FROM
      Landmark
    LEFT OUTER JOIN
      FancyLandmark
    ON
      Landmark.Id = FancyLandmark.Id
    WHERE
      Landmark.Id = Landmark.Id AND
      EXISTS (
        SELECT
          Town.*
        FROM
          Town
        WHERE
          Town.Id = Landmark.TownId AND
          Town.CountryId = 14
      )
  );

If you replace the DELETE with a SELECT * you'll see it selects everything. That seems to be because the nested SELECT * FROM Landmark is not aliased and masks the outer Landmark, so the join condition WHERE Landmark.Id = Landmark.Id fails to restrict the rows.

Is this a known issue, and are you aware of any workarounds?

Thanks for reporting this. Yes this behaviour is certainly not right, I have tracked down the issue here and have added in a fix. This will be available in the next nightly build for both 4.0 and 5.0.

Jeremy

Thanks, Jeremy. That resolved the issue.