Opened 4 years ago

Closed 4 years ago

#18304 closed Bug (fixed)

MySQL generates an unecessary select when updating inherited models

Reported by: Anssi Kääriäinen Owned by: anonymous
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords: mysql
Cc: Triage Stage: Ready for checkin
Has patch: yes Needs documentation: yes
Needs tests: yes Patch needs improvement: yes
Easy pickings: yes UI/UX: yes


The reason is in !SQLUpdateCompiler.pre_sql_setup(): it checks if there are multiple tables in the query, and if the backend can't do a self select on update ("update_can_self_select" db feature) the pre_sql_setup will run a query to fetch the pks to update. However, in inheritance cases this leads to queries like:

SELECT U0.`person_ptr_id` FROM `update_only_fields_employee` U0 WHERE U0.`person_ptr_id` = 2

which is non-necessary to run.

The problem was spotted when running update_only_fields tests on MySQL - the test will be skipped on MySQL, but the test_num_queries_inheritance test shows the problem if the skip is removed.

Change History (5)

comment:1 Changed 4 years ago by Anssi Kääriäinen

Avoided test failure on MySQL by skipping a failing test

MySQL generates an extra query in inheritance cases when doing an update.
This results in a test failure when checking for number of queries in
update_only_fields tests. Added a skip temporarily to avoid this test
failure. Refs #18304.

Changeset: de79d23ce04193e0bc140991533359002f62ddf9

comment:2 Changed 4 years ago by Anssi Kääriäinen

Needs documentation: unset
Needs tests: unset
Patch needs improvement: unset
Triage Stage: UnreviewedReady for checkin

comment:3 Changed 4 years ago by anonymous

Easy pickings: set
Has patch: set
Needs documentation: set
Needs tests: set
Patch needs improvement: set
UI/UX: set

comment:4 Changed 4 years ago by anonymous

Owner: changed from nobody to anonymous

comment:5 Changed 4 years ago by Anssi Kääriäinen <akaariai@…>

Resolution: fixed
Status: newclosed

In [d5c7f9efc3702888b556cbf932116421b464e8b8]:

Fixed #18304 -- Optimized save() when update_can_self_select=False

Databases with update_can_self_select = False (MySQL for example)
generated non-necessary queries when saving a multitable inherited
model, and when the save resulted in update.

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