toofishes.net

The agony of simple things in MySQL

Posted Feb. 18, 2011 at 9:34 a.m.

I try really hard not to rip on MySQL without good reason, but this stumble I encountered was beyond stupid.

mysql> SELECT DISTINCT pr.*, u.username, p.pkgbase
    -> FROM packages_packagerelation pr
    -> JOIN auth_user u ON pr.user_id = u.id
    -> LEFT JOIN packages p ON pr.pkgbase = p.pkgbase
    -> WHERE u.is_active = 0 OR p.pkgbase IS NULL;
...
328 rows in set (0.10 sec)

mysql> DELETE FROM packages_packagerelation
    -> WHERE id IN
    -> (SELECT pr.id
    -> FROM packages_packagerelation pr
    -> JOIN auth_user u ON pr.user_id = u.id
    -> LEFT JOIN packages p ON pr.pkgbase = p.pkgbase
    -> WHERE u.is_active = 0 OR p.pkgbase IS NULL);
ERROR 1093 (HY000): You can’t specify target table 'packages_packagerelation' for update in FROM clause

Really? Instead, you have to resort to silly workarounds to do the DELETE or UPDATE. The documentation even states "Currently, you cannot update a table and select from the same table in a subquery. " Someone please explain to me why MySQL requires the explicit derived/temporary table creation- it makes no sense why MySQL doesn't do this automatically in the first place.

mysql> DELETE FROM packages_packagerelation
    -> WHERE id IN
    -> (SELECT id FROM
    -> (SELECT pr.id FROM
    -> packages_packagerelation pr
    -> JOIN auth_user u ON pr.user_id = u.id
    -> LEFT JOIN packages p ON pr.pkgbase = p.pkgbase
    -> WHERE u.is_active = 0 OR p.pkgbase IS NULL) temptable);
Query OK, 328 rows affected (0.74 sec)

At least it is relatively easy to transform, but what a joke. I know at least one other database that doesn't stumble over this…

mysql

Comments (4)

Pierre on Feb. 18, 2011 at 10:07 a.m.:

Funny, I had the same problem like two weeks ago. There seems to be a general problem when using IN <subquery>. I don't remember the exact query but I run into performance issue because mysql evaluated the inner query for every row in the outer query. (It's a known bug btw.)

Maybe this is related because in that case deleting from the same table in the outer query may have strange results.

It's funny that your workaround does work though. Other options would be to just split the query into two or create a temporary table.

Dan McGee on Feb. 18, 2011 at 1:57 p.m.:

If you notice, I could have wrote the original query using two IN clauses in the WHERE clause; habit and MySQL's awful query planner made me write it as a JOIN instead to avoid the bug you mention. PostgreSQL's query planner would rewrite the IN to a JOIN without any problems, and not reevaluate it once per row.

It does make sense that this bug causes the UPDATE/DELETE restriction, I just find it incomprehensible they haven't made their query planner be smart enough to recognize independent subqueries and automatically wrap them like I had to do manually.

Jeroen on Feb. 21, 2011 at 7:24 a.m.:

I was reading this 3 days ago thinking, how less frequent it would happen. Today, I have this issue. ;-)

Thanks for the post!

Shanness on Sept. 12, 2011 at 1:05 a.m.:

Thanks very much for this clear post. You saved me heaps of time. Gotta say mysql is driving me crazy after being used to postgres. This restriction is very annoying.

The agony of simple things in MySQL

Posted Feb. 18, 2011 at 9:34 a.m.

Similar Posts

Comments (4)