Django support update limit

patchy.patch

From 8be1f398dfca15c45d34967edfc72fc951ec0cb1 Mon Sep 17 00:00:00 2001
From: Adam Chainz <adam@adamj.eu>
Date: Tue, 24 Feb 2015 18:56:47 +0000
Subject: [PATCH] Support delete limit

---
 django/db/models/query.py        |  4 ++--
 django/db/models/sql/compiler.py |  2 ++
 tests/update/tests.py            | 13 +++++++++++--
 3 files changed, 15 insertions(+), 4 deletions(-)

diff --git a/django/db/models/query.py b/django/db/models/query.py
index f7a4905..8b1c01d 100644
--- a/django/db/models/query.py
+++ b/django/db/models/query.py
@@ -610,8 +610,8 @@ class QuerySet(object):
         Updates all elements in the current QuerySet, setting all the given
         fields to the appropriate values.
         """
-        assert self.query.can_filter(), \
-            "Cannot update a query once a slice has been taken."
+        assert not self.query.low_mark, \
+            "Cannot use 'offset' with update."
         self._for_write = True
         query = self.query.clone(sql.UpdateQuery)
         query.add_update_values(kwargs)
diff --git a/django/db/models/sql/compiler.py b/django/db/models/sql/compiler.py
index 3ca2d6d..870d07d 100644
--- a/django/db/models/sql/compiler.py
+++ b/django/db/models/sql/compiler.py
@@ -1036,6 +1036,8 @@ class SQLUpdateCompiler(SQLCompiler):
         where, params = self.compile(self.query.where)
         if where:
             result.append('WHERE %s' % where)
+        if self.query.high_mark is not None:
+            result.append('LIMIT %d' % self.query.high_mark)
         return ' '.join(result), tuple(update_params + params)
 
     def execute_sql(self, result_type):
diff --git a/tests/update/tests.py b/tests/update/tests.py
index 1ed316c..63e6fa4 100644
--- a/tests/update/tests.py
+++ b/tests/update/tests.py
@@ -120,12 +120,21 @@ class AdvancedTests(TestCase):
 
     def test_update_slice_fail(self):
         """
-        We do not support update on already sliced query sets.
+        We do not support update on bottom sliced query sets.
         """
-        method = DataPoint.objects.all()[:2].update
+        method = DataPoint.objects.all()[1:2].update
         self.assertRaises(AssertionError, method,
                           another_value='another thing')
 
+    def test_update_slice_success(self):
+        """
+        We support update on when a top slice is set.
+        """
+        have_value = DataPoint.objects.filter(another_value='another thing')
+        self.assertEqual(have_value.count(), 0)
+        DataPoint.objects.all()[:1].update(another_value='another thing')
+        self.assertEqual(have_value.count(), 1)
+
     def test_update_respects_to_field(self):
         """
         Update of an FK field which specifies a to_field works.
-- 
2.3.0

This works on MySQL but apparently nothing else.

It has been discussed a bit for postgresql but not implemented:

• http://www.postgresql.org/message-id/20100929001628.f8547c49.bartek-sql@klolik.org

The typical solution appears to be a subquery:

• http://stackoverflow.com/questions/17552428/update-table-with-limit-and-offset-in-postgres

(This syntax doesn't work on MySQL since it doesn't support LIMIT in subqueries - http://dev.mysql.com/doc/refman/5.7/en/subquery-restrictions.html).

It's also apparently possible in sqlite if it's compiled in (https://www.sqlite.org/lang_update.html) but it's not compiled in on my up-to-date mac os x python version 😦

For completion, it appears to work on Oracle:

https://docs.oracle.com/cd/E17952_01/refman-5.5-en/update.html

My test runs:

MySQL

$ ./runtests.py -k update.tests --settings test_mysql
Testing against Django installed in '/Users/adamj/Documents/Projects/django/django'
Using existing test database for alias 'default'...
Using existing test database for alias 'other'...
.............
----------------------------------------------------------------------
Ran 13 tests in 0.200s

OK
Preserving test database for alias 'default'...
Preserving test database for alias 'other'...

Yay it works 😃 ❗

sqlite

$ ./runtests.py -k update.tests --settings test_sqlite
Testing against Django installed in '/Users/adamj/Documents/Projects/django/django'
Using existing test database for alias 'default'...
Using existing test database for alias 'other'...
.......E.....
======================================================================
ERROR: test_update_slice_success (update.tests.AdvancedTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/Users/adamj/Documents/Projects/django/tests/update/tests.py", line 135, in test_update_slice_success
    DataPoint.objects.all()[:1].update(another_value='another thing')
  File "/Users/adamj/Documents/Projects/django/django/db/models/query.py", line 619, in update
    rows = query.get_compiler(self.db).execute_sql(CURSOR)
  File "/Users/adamj/Documents/Projects/django/django/db/models/sql/compiler.py", line 1050, in execute_sql
    cursor = super(SQLUpdateCompiler, self).execute_sql(result_type)
  File "/Users/adamj/Documents/Projects/django/django/db/models/sql/compiler.py", line 829, in execute_sql
    cursor.execute(sql, params)
  File "/Users/adamj/Documents/Projects/django/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/Users/adamj/Documents/Projects/django/django/db/utils.py", line 92, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/Users/adamj/Documents/Projects/django/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/Users/adamj/Documents/Projects/django/django/db/backends/sqlite3/base.py", line 318, in execute
    return Database.Cursor.execute(self, query, params)
OperationalError: near "LIMIT": syntax error

----------------------------------------------------------------------
Ran 13 tests in 0.098s

FAILED (errors=1)
Preserving test database for alias 'default'...
Preserving test database for alias 'other'...

💩

Turns out it's recommended against due to nondeterminstic replication : http://mysql.rjweb.org/doc.php/deletebig#non_deterministic_replication

Maybe this is fixed by ROW/MIXED replication, but it's not something you should risk really.