Table reordering of multi-table DELETE

[Saverio Miroddi]

I have to delete records from some tables in parent-child relationship, with the constraint that the tables can't have the ON DELETE CASCADE option.

The reference of MySQL 5.0 clearly states that the query optimizer is free to reorder the tables, so that the behavior of deletion of parents and children is not deterministic:

DELETE c.*, p.*
FROM children c JOIN parents p ON p.id = c.parent_id
WHERE p.attribute = 'aaa'

this query could delete from table_b, leaving orphans in table_a.

But here's the catch.
DELETE accepts STRAIGHT JOINs, which are guaranteed to access the tables in the given order.
But even if I use them:

DELETE c.*, p.*
FROM children c STRAIGHT JOIN parents p ON p.id = c.parent_id
WHERE p.attribute = 'aaa'

The behavior is still non-deterministic.

Is this a bug?

The real query I used had 5 joined tables.



Edited 2 time(s). Last edit at 05/27/2009 02:11AM by Saverio Miroddi.