Opened 3 years ago

Closed 3 years ago

Last modified 3 years ago

#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 Mariusz Felisiak, 3 years ago

Cc: David Chorpash Adam Johnson Simon Charette added
Owner: changed from nobody to Mariusz Felisiak
Severity: NormalRelease blocker
Status: newassigned
Summary: order_by().update() support on MySQL / MariaDB fails with multi-table inheritanceorder_by().update() with joined fields crashes on MySQL/MariaDB.
Triage Stage: UnreviewedAccepted

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

Regression in 779e615e362108862f1681f965ee9e4f1d0ae6d2.
Reproduced at a77c9a4229cfef790ec18001b2cd18bd9c4aedbc.

comment:2 by Mariusz Felisiak, 3 years ago

Has patch: set

comment:3 by Matt Westcott, 3 years ago

PR looks good and works well for me, thanks!

comment:4 by Carlton Gibson, 3 years ago

Triage Stage: AcceptedReady for checkin

comment:5 by GitHub <noreply@…>, 3 years ago

Resolution: fixed
Status: assignedclosed

In ca987290:

Fixed #32645 -- Fixed QuerySet.update() crash when ordered by joined fields on MySQL/MariaDB.

Thanks Matt Westcott for the report.

Regression in 779e615e362108862f1681f965ee9e4f1d0ae6d2.

comment:6 by Mariusz Felisiak <felisiak.mariusz@…>, 3 years ago

In 208e7227:

[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

Note: See TracTickets for help on using tickets.
Back to Top