QuerySet ".update(...)" silently turns into SELECT+UPDATE? (MySQL)

Django Internals ORM
2

:wave: @bbkfhq!

This MySQL-only behavior is effectively not documented.

The reason why it happens is that the original QuerySet.update logic predates generalized support for UPDATE JOIN and thus took an approach where it turned any references to the non-target table in filter (and thus WHERE) into a subquery of the form

UPDATE
    `polls_choice`
SET
    `votes` = 100
WHERE
    `polls_choice`.`id` IN (
        SELECT
            `polls_choice`.`id`
        FROM
            `polls_choice`
            INNER JOIN `polls_question` ON (
                `polls_choice`.`question_id` = `polls_question`.`id`
            )
        WHERE (
            `polls_choice`.`id` = 1
            AND `polls_question`.`question_text` = 'TEST'
        )

The problem with this approach though is that MySQL doesn’t allow self-select updates (UPDATE table ... WHERE (SELECT ... FROM table)) so the ORM must materialize the list of IDs on the Python side and pass it back (opening a race condition window).

Ultimately I think the SQLUpdateCompiler logic should be refactored to make use of JOIN if supported instead and fallback to the IN (subquery) mechanism otherwise but that’s a lot work. In the mean time I think an admonition to the update documentation could be worthy.