toofishes.net

The agony of simple things in MySQL

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…

Tags

See Also