id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
36877	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 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 [https://dev.mysql.com/doc/refman/8.4/en/update.html 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."	Bug	new	Database layer (models, ORM)	6.0	Normal		mysql	Samir Shah	Unreviewed	0	0	0	0	0	0