Problem with MySQL subquery
The reason the first query does not work consistently has to do with how MySQL processes subqueries. In fact, subqueries will experience rewrites and transformations.

There are four(4) components explained here:


Item_in_optimizer
Item_in_subselect
Item_ref
Left_expression_Cache


From the examples posted, it would be impossible to allow an item_ref to become a self reference. In terms of your single DELETE query, the test table as a whole cannot fully self reference itself because some keys are available during transformation and some are not. Therefore, when a query performs a self-reference, a key (in this case id) can disappear in a transformation even though the actual self-referenced table has the key. 

Mysql subqueries are only great for sub-SELECTs, even self-referencing a table multiple times. The same cannot be said for non-SELECT queries.

I hope this explanation helps.