Problem with MySQL subquery

Why does this query

DELETE FROM test 
WHERE id = ( SELECT id 
             FROM (SELECT * FROM test) temp 
             ORDER BY RAND() 
             LIMIT 1
           );

sometimes delete 1 row, sometimes 2 rows and sometimes nothing?

If I write it in this form:

SET @var = ( SELECT id 
             FROM (SELECT * FROM test) temp 
             ORDER BY RAND() 
             LIMIT 1
           ); 
DELETE FROM test 
WHERE id=@var;

then it work correctly - is problem in 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.

StackExchange Database Administrators Q#1371, answer score: 13