Problem with MySQL subquery

Question

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?

Answer 1

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.

Answer 2

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

Answer 3

From the first bullet on this page, LIMIT is not supported in mysql subqueries. I'm not sure why it doesn't throw an error for you, though.