Fixed #31573 -- Made QuerySet.update() respect ordering on MariaDB/MySQL.

5 files changed, 79 insertions, 2 deletions

diff --git a/django/db/backends/mysql/compiler.py b/django/db/backends/mysql/compiler.py
index 3682d6da05..1b4d583fb0 100644
--- a/django/db/backends/mysql/compiler.py
+++ b/django/db/backends/mysql/compiler.py
@@ -1,3 +1,4 @@
+from django.core.exceptions import FieldError
 from django.db.models.sql import compiler
 
 
@@ -36,7 +37,23 @@ class SQLDeleteCompiler(compiler.SQLDeleteCompiler, SQLCompiler):
 
 
 class SQLUpdateCompiler(compiler.SQLUpdateCompiler, SQLCompiler):
-    pass
+    def as_sql(self):
+        update_query, update_params = super().as_sql()
+        # MySQL and MariaDB support UPDATE ... ORDER BY syntax.
+        if self.query.order_by:
+            order_by_sql = []
+            order_by_params = []
+            try:
+                for _, (sql, params, _) in self.get_order_by():
+                    order_by_sql.append(sql)
+                    order_by_params.extend(params)
+                update_query += ' ORDER BY ' + ', '.join(order_by_sql)
+                update_params += tuple(order_by_params)
+            except FieldError:
+                # Ignore ordering if it contains annotations, because they're
+                # removed in .update() and cannot be resolved.
+                pass
+        return update_query, update_params
 
 
 class SQLAggregateCompiler(compiler.SQLAggregateCompiler, SQLCompiler):
diff --git a/docs/ref/models/querysets.txt b/docs/ref/models/querysets.txt
index d70f7deca3..6edc508661 100644
--- a/docs/ref/models/querysets.txt
+++ b/docs/ref/models/querysets.txt
@@ -2530,6 +2530,21 @@ update a bunch of records for a model that has a custom
         e.comments_on = False
         e.save()
 
+Ordered queryset
+^^^^^^^^^^^^^^^^
+
+.. versionadded:: 3.2
+
+Chaining ``order_by()`` with ``update()`` is supported only on MariaDB and
+MySQL, and is ignored for different databases. This is useful for updating a
+unique field in the order that is specified without conflicts. For example::
+
+    Entry.objects.order_by('-number').update(number=F('number') + 1)
+
+.. note::
+
+    If the ``order_by()`` clause contains annotations, it will be ignored.
+
 ``delete()``
 ~~~~~~~~~~~~
 
diff --git a/docs/releases/3.2.txt b/docs/releases/3.2.txt
index 88c4cdc998..6c49cf5375 100644
--- a/docs/releases/3.2.txt
+++ b/docs/releases/3.2.txt
@@ -224,6 +224,9 @@ Models
 * The new :attr:`.UniqueConstraint.opclasses` attribute allows setting
   PostgreSQL operator classes.
 
+* The :meth:`.QuerySet.update` method now respects the ``order_by()`` clause on
+  MySQL and MariaDB.
+
 Requests and Responses
 ~~~~~~~~~~~~~~~~~~~~~~
 
diff --git a/tests/update/models.py b/tests/update/models.py
index 7861987604..98f40a8603 100644
--- a/tests/update/models.py
+++ b/tests/update/models.py
@@ -41,3 +41,7 @@ class Foo(models.Model):
 class Bar(models.Model):
     foo = models.ForeignKey(Foo, models.CASCADE, to_field='target')
     m2m_foo = models.ManyToManyField(Foo, related_name='m2m_foo')
+
+
+class UniqueNumber(models.Model):
+    number = models.IntegerField(unique=True)
diff --git a/tests/update/tests.py b/tests/update/tests.py
index abf4db11d9..90844ccb6f 100644
--- a/tests/update/tests.py
+++ b/tests/update/tests.py
@@ -1,9 +1,12 @@
+import unittest
+
 from django.core.exceptions import FieldError
+from django.db import IntegrityError, connection, transaction
 from django.db.models import Count, F, Max
 from django.db.models.functions import Concat, Lower
 from django.test import TestCase
 
-from .models import A, B, Bar, D, DataPoint, Foo, RelatedPoint
+from .models import A, B, Bar, D, DataPoint, Foo, RelatedPoint, UniqueNumber
 
 
 class SimpleTest(TestCase):
@@ -199,3 +202,38 @@ class AdvancedTests(TestCase):
             with self.subTest(annotation=annotation):
                 with self.assertRaisesMessage(FieldError, msg):
                     RelatedPoint.objects.annotate(new_name=annotation).update(name=F('new_name'))
+
+
+@unittest.skipUnless(
+    connection.vendor == 'mysql',
+    'UPDATE...ORDER BY syntax is supported on MySQL/MariaDB',
+)
+class MySQLUpdateOrderByTest(TestCase):
+    """Update field with a unique constraint using an ordered queryset."""
+    @classmethod
+    def setUpTestData(cls):
+        UniqueNumber.objects.create(number=1)
+        UniqueNumber.objects.create(number=2)
+
+    def test_order_by_update_on_unique_constraint(self):
+        tests = [
+            ('-number', 'id'),
+            (F('number').desc(), 'id'),
+            (F('number') * -1, 'id'),
+        ]
+        for ordering in tests:
+            with self.subTest(ordering=ordering), transaction.atomic():
+                updated = UniqueNumber.objects.order_by(*ordering).update(
+                    number=F('number') + 1,
+                )
+                self.assertEqual(updated, 2)
+
+    def test_order_by_update_on_unique_constraint_annotation(self):
+        # Ordering by annotations is omitted because they cannot be resolved in
+        # .update().
+        with self.assertRaises(IntegrityError):
+            UniqueNumber.objects.annotate(
+                number_inverse=F('number').desc(),
+            ).order_by('number_inverse').update(
+                number=F('number') + 1,
+            )