[Django] #32645: order_by().update() support on MySQL / MariaDB fails with multi-table inheritance

[Django]

#32645: order_by().update() support on MySQL / MariaDB fails with multi-table
inheritance
-------------------------------------+-------------------------------------
Reporter: Matt | Owner: nobody
Westcott |
Type: Bug | Status: new
Component: Database | Version: 3.2
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
The support for respecting queryset ordering on update queries in MySQL /
MariaDB (added in #31573, 779e615e362108862f1681f965ee9e4f1d0ae6d2) does
not account for multi-table inheritance setups where the columns in the
ordering exist in a different table from the one being updated. This
causes failures on some queries that worked prior to Django 3.2.

Testing against MySQL 8.0.23, and given the model definitions:
{{{
class Place(models.Model):
name = models.CharField(max_length=255)

class Restaurant(Place):
stars = models.IntegerField()
}}}

the query `Restaurant.objects.order_by('name').update(stars=3)` fails with
`django.db.utils.OperationalError: (1054, "Unknown column
'core_place.name' in 'order clause'")`. (Obviously in this example the
`order_by` clause is somewhat spurious, but in a real-world setup it could
be introduced by a custom manager on the Place model, for example.)

Meanwhile, `Restaurant.objects.order_by('name').update(name='Pizza Hut')`
fails with `django.db.utils.ProgrammingError: (1064, "You have an error in
your SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near 'ORDER BY `core_place`.`name`
ASC' at line 1")`. In this case, the base SQLUpdateCompiler class returns
an empty string to denote that no UPDATE is needed on the `restaurant`
table (the UPDATE on `place` would happen in a subsequent call to
`as_sql`), but the MySQL backend is appending the ORDER BY clause to that
empty string.

--
Ticket URL: <https://code.djangoproject.com/ticket/32645>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#32645: order_by().update() with joined fields crashes on MySQL/MariaDB.
-------------------------------------+-------------------------------------
Reporter: Matt Westcott | Owner: Mariusz
| Felisiak
Type: Bug | Status: assigned
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Release blocker | 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):

* status: new => assigned
* severity: Normal => Release blocker
* cc: David Chorpash, Adam Johnson, Simon Charette (added)
* owner: nobody => Mariusz Felisiak
* stage: Unreviewed => Accepted

Comment:

Thanks for this report. We should ignore `order_by()` clauses with joined
fields.

Regression in 779e615e362108862f1681f965ee9e4f1d0ae6d2.
Reproduced at a77c9a4229cfef790ec18001b2cd18bd9c4aedbc.

--
Ticket URL: <https://code.djangoproject.com/ticket/32645#comment:1>

[Django]

#32645: order_by().update() with joined fields crashes on MySQL/MariaDB.
-------------------------------------+-------------------------------------
Reporter: Matt Westcott | Owner: Mariusz
| Felisiak
Type: Bug | Status: assigned
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Accepted

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* has_patch: 0 => 1

Comment:

[https://github.com/django/django/pull/14265 PR]

--
Ticket URL: <https://code.djangoproject.com/ticket/32645#comment:2>

[Django]

#32645: order_by().update() with joined fields crashes on MySQL/MariaDB.
-------------------------------------+-------------------------------------
Reporter: Matt Westcott | Owner: Mariusz
| Felisiak
Type: Bug | Status: assigned
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Accepted

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Matt Westcott):

PR looks good and works well for me, thanks!

--
Ticket URL: <https://code.djangoproject.com/ticket/32645#comment:3>

[Django]

#32645: order_by().update() with joined fields crashes on MySQL/MariaDB.
-------------------------------------+-------------------------------------
Reporter: Matt Westcott | Owner: Mariusz
| Felisiak
Type: Bug | Status: assigned
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Release blocker | Resolution:

Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Carlton Gibson):

* stage: Accepted => Ready for checkin

--
Ticket URL: <https://code.djangoproject.com/ticket/32645#comment:4>

[Django]

#32645: order_by().update() with joined fields crashes on MySQL/MariaDB.
-------------------------------------+-------------------------------------
Reporter: Matt Westcott | Owner: Mariusz
| Felisiak

Type: Bug | Status: closed

Component: Database layer | Version: 3.2
(models, ORM) |

Severity: Release blocker | Resolution: fixed

Keywords: | Triage Stage: Ready for
| checkin

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by GitHub <noreply@…>):

* status: assigned => closed
* resolution: => fixed

Comment:

In [changeset:"ca9872905559026af82000e46cde6f7dedc897b6" ca987290]:
{{{
#!CommitTicketReference repository=""
revision="ca9872905559026af82000e46cde6f7dedc897b6"
Fixed #32645 -- Fixed QuerySet.update() crash when ordered by joined
fields on MySQL/MariaDB.

Thanks Matt Westcott for the report.

Regression in 779e615e362108862f1681f965ee9e4f1d0ae6d2.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/32645#comment:5>

[Django]

#32645: order_by().update() with joined fields crashes on MySQL/MariaDB.
-------------------------------------+-------------------------------------
Reporter: Matt Westcott | Owner: Mariusz
| Felisiak
Type: Bug | Status: closed
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"208e72276a3e12a4e7998b9a1219bc96a16cf7b8" 208e7227]:
{{{
#!CommitTicketReference repository=""
revision="208e72276a3e12a4e7998b9a1219bc96a16cf7b8"
[3.2.x] Fixed #32645 -- Fixed QuerySet.update() crash when ordered by
joined fields on MySQL/MariaDB.

Thanks Matt Westcott for the report.

Regression in 779e615e362108862f1681f965ee9e4f1d0ae6d2.
Backport of ca9872905559026af82000e46cde6f7dedc897b6 from main
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/32645#comment:6>