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.
I think that the reason why it doesn't work as expected is not how MySQL processes subqueries but how MySQL processes UPDATE
statements. The statement:
DELETE
FROM test
WHERE id =
( SELECT id
FROM
( SELECT *
FROM test
) temp
ORDER BY RAND()
LIMIT 1
)
will process the WHERE
condition row by row. Meaning, for every row, it will run the subquery and test the result against id
:
( SELECT id
FROM
( SELECT *
FROM test
) temp
ORDER BY RAND()
LIMIT 1
)
So, it will occasionally match (and delete) 0, 1, 2 or even more rows!
You could rewrite it like this and the subquery will be processed once:
DELETE t
FROM
test t
JOIN
( SELECT id
FROM test
ORDER BY RAND()
LIMIT 1
) tmp
ON tmp.id = t.id