MySQL fast-delete optimizations introduced in Django 3.1 don't properly deal with aggregation and cause performance regression.

[Simon Charette]

​As reported on Github the fast-delete optimization on MySQL to use the proprietary DELETE FROM syntax introduced in #23576 and the follow up commit to disable it on MariaDB 10.3.1+ 5b83bae031a9771d86933bcc808d4bf2e21216a2 are causing performance regression due it's poor query planing of DELETE involving subqueries.

​While investigating the initially reported failure on MariaDB I noticed that the MySQL's SQLDeleteCompiler implementation was also not properly dealing while filters against aggregation. It's normally not an issue because aggregation is done against against many-to-many relationships that involve reverse ForeignKey that usually have an on_delete that require in-memory fetching from deletion.Collector (e.g. CASCADE, SET_NULL) and thus disable fast-delete. However when only fast-delete on_delete handlers are involved (e.g. DO_NOTHING or future database level on_delete support #21961) this can be an issue that results in a Invalid use of group function crash due to an attempt at using aggregate functions in the WHERE clause.

In order to address both issues I suggest we

1. Always use the subquery approach when aggregation is involved
2. Materialize the subquery at the database level when the backend doesn't have the update_can_self_select feature
3. Favor the proprietary DELETE FROM syntax otherwise to work around MySQL poor handling of subqueries and avoid the generation of slow queries.

[Mariusz Felisiak]

​PR

[Mariusz Felisiak <felisiak.mariusz@…>]

In f6405c0:

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.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 2986ec03:

[3.1.x] 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.

Backport of f6405c0b8ef7aff513b105c1da68407a881a3671 from master