Fixed #31965 -- Adjusted multi-table fast-deletion on MySQL/MariaDB.

The optimization introduced in 7acef095d73 did not properly handle
deletion involving filters against aggregate annotations.

It initially was surfaced by a MariaDB test failure but misattributed
to an undocumented change in behavior that resulted in the systemic
generation of poorly performing database queries in 5b83bae031.

Thanks Anton Plotkin for the report.

Refs #23576.

1 files changed, 12 insertions, 10 deletions

diff --git a/django/db/backends/mysql/compiler.py b/django/db/backends/mysql/compiler.py
index 1b4d583fb0..da02c1fd73 100644
--- a/django/db/backends/mysql/compiler.py
+++ b/django/db/backends/mysql/compiler.py
@@ -16,13 +16,15 @@ class SQLInsertCompiler(compiler.SQLInsertCompiler, SQLCompiler):
 
 class SQLDeleteCompiler(compiler.SQLDeleteCompiler, SQLCompiler):
     def as_sql(self):
-        if self.connection.features.update_can_self_select or self.single_alias:
+        # Prefer the non-standard DELETE FROM syntax over the SQL generated by
+        # the SQLDeleteCompiler's default implementation when multiple tables
+        # are involved since MySQL/MariaDB will generate a more efficient query
+        # plan than when using a subquery.
+        where, having = self.query.where.split_having()
+        if self.single_alias or having:
+            # DELETE FROM cannot be used when filtering against aggregates
+            # since it doesn't allow for GROUP BY and HAVING clauses.
             return super().as_sql()
-        # MySQL and MariaDB < 10.3.2 doesn't support deletion with a subquery
-        # which is what the default implementation of SQLDeleteCompiler uses
-        # when multiple tables are involved. Use the MySQL/MariaDB specific
-        # DELETE table FROM table syntax instead to avoid performing the
-        # operation in two queries.
         result = [
             'DELETE %s FROM' % self.quote_name_unless_alias(
                 self.query.get_initial_alias()
@@ -30,10 +32,10 @@ class SQLDeleteCompiler(compiler.SQLDeleteCompiler, SQLCompiler):
         ]
         from_sql, from_params = self.get_from_clause()
         result.extend(from_sql)
-        where, params = self.compile(self.query.where)
-        if where:
-            result.append('WHERE %s' % where)
-        return ' '.join(result), tuple(from_params) + tuple(params)
+        where_sql, where_params = self.compile(where)
+        if where_sql:
+            result.append('WHERE %s' % where_sql)
+        return ' '.join(result), tuple(from_params) + tuple(where_params)
 
 
 class SQLUpdateCompiler(compiler.SQLUpdateCompiler, SQLCompiler):