Code

Opened 2 years ago

Closed 2 years ago

#18304 closed Bug (fixed)

MySQL generates an unecessary select when updating inherited models

Reported by: akaariai 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

Description

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.

Attachments (0)

Change History (5)

comment:1 Changed 2 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 2 years ago by akaariai

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Ready for checkin

comment:3 Changed 2 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 2 years ago by anonymous

  • Owner changed from nobody to anonymous

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

  • Resolution set to fixed
  • Status changed from new to closed

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.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.