#33759: Using subquery to filter a model delete generates a sub-optimal SQL
-------------------------------------+-------------------------------------
Reporter: Christofer Bertonha | Owner: nobody
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Mariusz Felisiak):
* stage: Unreviewed => Accepted
Comment:
We could add a new feature flag to add an extra subquery only on MySQL:
{{{#!diff
diff --git a/django/db/backends/base/features.py
b/django/db/backends/base/features.py
index c54d30cf73..e8737aadc2 100644
--- a/django/db/backends/base/features.py
+++ b/django/db/backends/base/features.py
@@ -12,6 +12,9 @@ class BaseDatabaseFeatures:
allows_group_by_selected_pks = False
empty_fetchmany_value = []
update_can_self_select = True
+ # Does the backend support self-reference subqueries in the DELETE
+ # statement?
+ delete_can_self_reference_subquery = True
# Does the backend distinguish between '' and None?
interprets_empty_strings_as_nulls = False
diff --git a/django/db/backends/mysql/features.py
b/django/db/backends/mysql/features.py
index 3ea3deeae3..848d891a84 100644
--- a/django/db/backends/mysql/features.py
+++ b/django/db/backends/mysql/features.py
@@ -25,6 +25,7 @@ class DatabaseFeatures(BaseDatabaseFeatures):
supports_slicing_ordering_in_compound = True
supports_index_on_text_field = False
supports_update_conflicts = True
+ delete_can_self_reference_subquery = False
create_test_procedure_without_params_sql = """
CREATE PROCEDURE test_procedure ()
BEGIN
diff --git a/django/db/models/sql/compiler.py
b/django/db/models/sql/compiler.py
index 9c7bd8ea1a..e57a43ac47 100644
--- a/django/db/models/sql/compiler.py
+++ b/django/db/models/sql/compiler.py
@@ -1721,7 +1721,10 @@ class SQLDeleteCompiler(SQLCompiler):
Create the SQL for this query. Return the SQL string and list of
parameters.
"""
- if self.single_alias and not
self.contains_self_reference_subquery:
+ if self.single_alias and (
+ self.connection.features.delete_can_self_reference_subquery
or
+ not self.contains_self_reference_subquery
+ ):
return self._as_sql(self.query)
innerq = self.query.clone()
innerq.__class__ = Query
}}}
Another issue is that `QuerySet.delete()` from the ticket description:
{{{
subquery = Comment.objects.filter(created_at__lt=datetime(2022, 6,
1))[:1000]
Comment.objects.filter(id__in=subquery).delete()
...
File "site-packages/MySQLdb/connections.py", line 254, in query
_mysql.connection.query(self, query)
django.db.utils.NotSupportedError: (1235, "This version of MySQL doesn't
yet support 'LIMIT & IN/ALL/ANY/SOME subquery'")
}}}
crashes on MySQL even with 4074f38e1dcc93b859bbbfd6abd8441c3bca36b3.
--
Ticket URL: <https://code.djangoproject.com/ticket/33759#comment:5>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/0107018122c0d8cd-ece375b4-40f9-4ddf-99c9-4053ab5b3c3b-000000%40eu-central-1.amazonses.com.
