[Django] #36877: Order of update operations behaves differently on MySQL compared to other databases

3 views

Skip to first unread message

Django

unread,

6:24 AM (15 hours ago) 6:24 AM

to django-...@googlegroups.com

#36877: Order of update operations behaves differently on MySQL compared to other
databases
-------------------------------------+-------------------------------------
Reporter: Samir Shah | Type: Bug
Status: new | Component: Database
| layer (models, ORM)
Version: 6.0 | Severity: Normal
Keywords: mysql | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/36877>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,

6:31 AM (15 hours ago) 6:31 AM

to django-...@googlegroups.com

#36877: Order of update operations behaves differently on MySQL compared to other
databases
-------------------------------------+-------------------------------------

Description changed by Samir Shah:

Old description:

New description:

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:

{{{