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

[Django]

#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]

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

Reporter: Samir Shah | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 6.0
(models, ORM) |
Severity: Normal | Resolution:

Keywords: mysql | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

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:

{{{

from django.db.models.functions import Length

--
Ticket URL: <https://code.djangoproject.com/ticket/36877#comment:1>

[Django]

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

--------------------------------------+------------------------------------

Reporter: Samir Shah | Owner: (none)

Type: Cleanup/optimization | Status: new
Component: Documentation | Version: 6.0
Severity: Normal | Resolution:
Keywords: mysql | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 1
Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0

--------------------------------------+------------------------------------
Changes (by Simon Charette):

* component: Database layer (models, ORM) => Documentation
* needs_better_patch: 0 => 1
* needs_docs: 0 => 1
* stage: Unreviewed => Accepted
* type: Bug => Cleanup/optimization

Comment:

Thank you for your report Samir, I could swear I dropped a message here
already but I think we should won't fix or focus it as a documentation
admonition

Because `update(**fields)` maintains the user specified order I think we
must keep things as is to allow either `NEW` or `OLD` tuple values to be
targetted. Changing the default behaviour on MySQL seems clever but it
suffers from a few problems

1. It's backward incompatible, users might be relying on this MySQL'ism
with explicit `**fields` ordering just like they do with `order_by` (see
#31573)
2. Forcing a particular order is error prone as there could be inter-
dependency that only the user know about between the updated fields
3. It opens a can of worms for other interfaces that use `update` such as
`Model.save` which defauls to the order of definition of the model fields

For these reasons I think we're better off keeping things as they are as
they allow the user to have more control over the expressiveness of the
resulting `UPDATE` query which is a property the ORM maintained since
`dict` preserve insertion order (Python 3.7+ in 2018).
--
Ticket URL: <https://code.djangoproject.com/ticket/36877#comment:2>

[Django]

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

-------------------------------------+-------------------------------------
Reporter: Samir Shah | Owner: Samir
Type: | Shah
Cleanup/optimization | Status: assigned

Component: Documentation | Version: 6.0
Severity: Normal | Resolution:
Keywords: mysql | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 1
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0

-------------------------------------+-------------------------------------
Changes (by Samir Shah):

* owner: (none) => Samir Shah
* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/36877#comment:3>

[Django]

#36877: Order of update operations behaves differently on MySQL compared to other
databases
-------------------------------------+-------------------------------------
Reporter: Samir Shah | Owner: Samir
Type: | Shah
Cleanup/optimization | Status: assigned
Component: Documentation | Version: 6.0
Severity: Normal | Resolution:
Keywords: mysql | Triage Stage: Accepted

Has patch: 1 | Needs documentation: 1

Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Samir Shah):

* has_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/36877#comment:4>

[Django]

#36877: Order of update operations behaves differently on MySQL compared to other
databases
-------------------------------------+-------------------------------------
Reporter: Samir Shah | Owner: Samir
Type: | Shah
Cleanup/optimization | Status: assigned
Component: Documentation | Version: 6.0
Severity: Normal | Resolution:
Keywords: mysql | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 1
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Samir Shah):

[https://github.com/django/django/pull/20604 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/36877#comment:5>