#32645 closed Bug (fixed)
order_by().update() with joined fields crashes on MySQL/MariaDB.
Reported by: | Matt Westcott | Owned by: | Mariusz Felisiak |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 3.2 |
Severity: | Release blocker | Keywords: | |
Cc: | David Chorpash, Adam Johnson, Simon Charette | Triage Stage: | Ready for checkin |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
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.
Change History (6)
comment:1 by , 3 years ago
Cc: | added |
---|---|
Owner: | changed from | to
Severity: | Normal → Release blocker |
Status: | new → assigned |
Summary: | order_by().update() support on MySQL / MariaDB fails with multi-table inheritance → order_by().update() with joined fields crashes on MySQL/MariaDB. |
Triage Stage: | Unreviewed → Accepted |
comment:4 by , 3 years ago
Triage Stage: | Accepted → Ready for checkin |
---|
Thanks for this report. We should ignore
order_by()
clauses with joined fields.Regression in 779e615e362108862f1681f965ee9e4f1d0ae6d2.
Reproduced at a77c9a4229cfef790ec18001b2cd18bd9c4aedbc.