Order of update operations behaves differently on MySQL compared to other databases

[Samir Shah]

There is a quirk in how MySQL handles update queries that means you can get inconsistent results when updating fields that derive values from one another. Here is an example:

class Entity:
    name = models.CharField(max_length=100)
    name_length = models.PositiveSmallIntegerField(default=0)

Now say you have an object in the database:

Entity.objects.create(name="Bob", name_length=3)

and then you run this update query:

from django.db.models.functions import Length
Entity.objects.update(name="Alice", name_length=Length("name"))

As per the SQL specification, the update is atomic, so that the length is computed using the *original* value of name. Thus in databases like PostgreSQL and SQLite this query will result in name_length being assigned the value 3. MySQL however, behaves differently, and uses the *new* value of name, resulting in the name_length being assigned the value 5.

This is ​documented:

If you access a column from the table to be updated in an expression, UPDATE uses the current value of the column. For example, the following statement sets col1 to one more than its current value:

UPDATE t1 SET col1 = col1 + 1;

The second assignment in the following statement sets col2 to the current (updated) col1 value, not the original col1 value. The result is that col1 and col2 have the same value. This behaviour differs from standard SQL.

UPDATE t1 SET col1 = col1 + 1, col2 = col1;

Single-table UPDATE assignments are generally evaluated from left to right.

Of note is the last comment in particular. This means the following two queries will yield different results:

Entity.objects.update(name="Alice", name_length=Length("name"))  # name_length will be set to 5 in MySQL
Entity.objects.update(name_length=Length("name"), name="Alice")  # name_length will be set to 3 in MySQL, because it was updated before name was changed

So we have two issues:

1. ORM queries run in this fashion behave differently on MySQL
2. MySQL is sensitive to the order in which kwargs are supplied to update(). This can very easily lead to gotchas that are hard to track down.

I do not know whether it is possible to apply any workarounds in Django so that the ORM behaviour is consistent with other databases - if it is, they may be worth attempting. If not, this might be at least worth documenting as a quirk with MySQL? I have tentatively categorised this as a bug but recognise that it may not be fixable as one.