Broken delete query with joins

[moritz89]

Have you tested delete queries when using inner joins? For me it results in SQL commands that delete more than they should. Given the following models. As a note, I don't directly use this library.

class Controller(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    name = models.CharField(max_length=255)

class Peripheral(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    name = models.CharField(max_length=255)
    controller = models.ForeignKey(
        Controller, on_delete=models.CASCADE, related_name="peripheral_set"
    )

class DataPoint(models.Model):
    time = models.DateTimeField(primary_key=True)
    value = models.FloatField()
    peripheral = models.ForeignKey(
        Peripheral, on_delete=models.CASCADE, related_name="data_point_set"
    )

When running the following delete command DataPoint.objects.filter(peripheral__controller=controller).delete() it results in the following SQL query:

DELETE
FROM "iot_datapoint"
WHERE "iot_datapoint"."time" IN
    (SELECT U0."time"
     FROM "iot_datapoint" U0
     INNER JOIN "iot_peripheral" U1 ON (U0."peripheral_id" = U1."id")
     WHERE U1."controller_id" = 52dd4eea-e184-4f94-85de-bdd8490d5f9d)

When testing with two controllers that have independent peripherals and data points, all data points from both are deleted. The deletion works properly if controller.delete() is called. However, this approach does not work when having to manually deal with compressed data.

A work-around is to manually write the delete state. The following SQL delete command works:

DELETE
FROM "iot_datapoint" B USING "iot_peripheral" C
WHERE B.peripheral_id = C.id
  AND C.controller_id = '6dece05b007f41c7b16eada01ac382a0'::UUID;