Re: [Django] #36213: Document that QuerySet.update can execute two separate SQL queries when using MySQL

[Django]

#36213: Document that QuerySet.update can execute two separate SQL queries when
using MySQL
-------------------------------------+-------------------------------------
Reporter: Babak | Owner: (none)
Type: | Status: new
Cleanup/optimization |
Component: Documentation | Version: dev
Severity: Normal | Resolution:
Keywords: mysql self-select | Triage Stage: Accepted
race-condition |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Anders Hovmöller):

This seems like it's the same thing:
https://www.reddit.com/r/django/comments/1ixb489/high_memory_usage_on_delete/

We also had a user on the unofficial django discord asking about a similar
situation where their process got killed by the OOM killer when doing an
update(). Also on mysql.
--
Ticket URL: <https://code.djangoproject.com/ticket/36213#comment:4>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#36213: Document that QuerySet.update can execute two separate SQL queries when
using MySQL
-------------------------------------+-------------------------------------
Reporter: Babak | Owner: (none)
Type: | Status: new
Cleanup/optimization |
Component: Documentation | Version: dev
Severity: Normal | Resolution:
Keywords: mysql self-select | Triage Stage: Accepted
race-condition |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by John Speno):

* cc: John Speno (added)

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

[Django]

#36213: Document that QuerySet.update can execute two separate SQL queries when
using MySQL
-------------------------------------+-------------------------------------
Reporter: Babak | Owner: (none)
Type: | Status: new
Cleanup/optimization |
Component: Documentation | Version: dev
Severity: Normal | Resolution:
Keywords: mysql self-select | Triage Stage: Accepted
race-condition |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Simon Charette):

> This seems like it's the same thing:
​https://www.reddit.com/r/django/comments/1ixb489/high_memory_usage_on_delete/

Unless they are making use of `on_delete=models.SET_NULL` or `models.SET`
which make use of `update` it's not the same thing.
--
Ticket URL: <https://code.djangoproject.com/ticket/36213#comment:6>

[Django]

#36213: Document that QuerySet.update can execute two separate SQL queries when
using MySQL
-------------------------------------+-------------------------------------
Reporter: Babak | Owner: (none)
Type: | Status: new
Cleanup/optimization |
Component: Documentation | Version: dev
Severity: Normal | Resolution:
Keywords: mysql self-select | Triage Stage: Accepted
race-condition |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Simon Charette):

In stand corrected, `QuerySet.delete` on MySQL
[https://github.com/django/django/blob/5a1cae3a5675c5733daf5949759476d65aa0e636/django/db/models/sql/compiler.py#L2015-L2019
also performs the] same materialization when a referenced table is
involved.
--
Ticket URL: <https://code.djangoproject.com/ticket/36213#comment:7>

[Django]

#36213: Document that QuerySet.update can execute two separate SQL queries when
using MySQL
-------------------------------------+-------------------------------------
Reporter: Babak | Owner: (none)
Type: | Status: new
Cleanup/optimization |
Component: Documentation | Version: dev
Severity: Normal | Resolution:
Keywords: mysql self-select | Triage Stage: Accepted
race-condition |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Simon Charette):

I confirmed that the Python side materialization of the query can be
entirely avoided by using the same strategy as #31965
(f6405c0b8ef7aff513b105c1da68407a881a3671).

While MySQL disallows `UPDATE table ... WHERE table.id IN (SELECT id FROM
table WHERE ...)` it allows `UPDATE table ... WHERE table.id IN (SELECT *
FROM (SELECT id FROM table WHERE ...) subquery)` as it materialize the
subqueries remotely.

I also learned that MySQL `UPDATE`
[https://www.sqlite.org/lang_update.html#update_from_in_other_sql_database_engines
supports updating multiple table at a time which is wild] so I figured I'd
give a shot making use of it.

Not sure if this changes the outcome of this ticket but
[https://github.com/django/django/compare/main...charettes:django:mysql-
update-tweaks the first two commits of this branch seem to address the
issue entirely] by

1. Materializing the subquery on the server side under all circumstances
2. In cases where the `UPDATE FROM` syntax can be used with `JOIN`s to
avoid the materialization of a subquery (which MySQL is notably bad at)
adopt a similar strategy to #23576
(7acef095d73322f45dcceb99afa1a4e50b520479). That's possible when no
aggregation or related updates are used.

The last commit is complementary but demonstrate an optimization for MTI
updates of the form

{{{#!python
Child.objects.filter(child_field=0).update(parent_field=1, child_field=2)
}}}

That are currently implemented through two N + 1 queries on all backends

{{{#!sql
SELECT parent_prt_id FROM child WHERE child_field = 0
UPDATE child SET child_field = 1 WHERE parent_prt_id IN :parent_prt_ids
UPDATE parent SET parent_field = 2 WHERE id IN :parent_prt_ids
}}}

But are replaced by the following on MySQL

{{{#!sql
UPDATE child
JOIN parent ON (child.parent_ptr_id = parent.id)
SET child.child_field = 1, parent.parent_field = 2
WHERE child_field = 0
}}}

Note that the optimization must be disabled if `order_by` is used against
inherited fields as MySQL doesn't allow references to `JOIN`'ed table in
`UPDATE ORDER BY` (the join clause would have to be altered to be an
ordered subquery).
--
Ticket URL: <https://code.djangoproject.com/ticket/36213#comment:8>

[Django]

#36213: Document that QuerySet.update can execute two separate SQL queries when
using MySQL
-------------------------------------+-------------------------------------

Reporter: Babak | Owner: edc
Type: | Status: assigned

Cleanup/optimization |
Component: Documentation | Version: dev
Severity: Normal | Resolution:
Keywords: mysql self-select | Triage Stage: Accepted
race-condition |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by edc):

* owner: (none) => edc
* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/36213#comment:9>

[Django]

#36213: Document that QuerySet.update can execute two separate SQL queries when
using MySQL
-------------------------------------+-------------------------------------
Reporter: Babak | Owner: edc
Type: | Status: assigned
Cleanup/optimization |
Component: Documentation | Version: dev
Severity: Normal | Resolution:
Keywords: mysql self-select | Triage Stage: Accepted
race-condition |

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by edc):

* has_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/36213#comment:10>

[Django]

#36213: Document that QuerySet.update can execute two separate SQL queries when
using MySQL
-------------------------------------+-------------------------------------
Reporter: Babak | Owner: edc
Type: | Status: assigned
Cleanup/optimization |
Component: Documentation | Version: dev
Severity: Normal | Resolution:
Keywords: mysql self-select | Triage Stage: Accepted
race-condition |
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Jacob Walls):

* needs_better_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/36213#comment:11>

[Django]

#36213: Document that QuerySet.update can execute two separate SQL queries when
using MySQL
-------------------------------------+-------------------------------------

Reporter: Babak | Owner: Babak

Type: | Status: assigned
Cleanup/optimization |
Component: Documentation | Version: dev
Severity: Normal | Resolution:

Keywords: mysql self-select | Triage Stage: Ready for
race-condition | checkin

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Jacob Walls):

* needs_better_patch: 1 => 0
* owner: edc => Babak
* stage: Accepted => Ready for checkin

Comment:

[https://github.com/django/django/pull/19312 New PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/36213#comment:12>

[Django]

#36213: Document that QuerySet.update can execute two separate SQL queries when
using MySQL
-------------------------------------+-------------------------------------
Reporter: Babak | Owner: Babak
Type: | Status: assigned
Cleanup/optimization |
Component: Documentation | Version: dev
Severity: Normal | Resolution:

Keywords: mysql self-select | Triage Stage: Accepted
race-condition |

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Sarah Boyce):

* needs_better_patch: 0 => 1

* stage: Ready for checkin => Accepted

--
Ticket URL: <https://code.djangoproject.com/ticket/36213#comment:13>

[Django]

#36213: Document that QuerySet.update can execute two separate SQL queries when
using MySQL
-------------------------------------+-------------------------------------
Reporter: Babak | Owner: Babak
Type: | Status: assigned
Cleanup/optimization |
Component: Documentation | Version: dev
Severity: Normal | Resolution:

Keywords: mysql self-select | Triage Stage: Ready for
race-condition | checkin

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Sarah Boyce):

* needs_better_patch: 1 => 0

* stage: Accepted => Ready for checkin

--
Ticket URL: <https://code.djangoproject.com/ticket/36213#comment:14>

[Django]

#36213: Document that QuerySet.update can execute two separate SQL queries when
using MySQL
-------------------------------------+-------------------------------------
Reporter: Babak | Owner: Babak

Type: | Status: closed

Cleanup/optimization |
Component: Documentation | Version: dev

Severity: Normal | Resolution: fixed

Keywords: mysql self-select | Triage Stage: Ready for
race-condition | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Sarah Boyce <42296566+sarahboyce@…>):

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

Comment:

In [changeset:"be1b776ad8d6f9bccfbdf63f84b16fb81a13119e" be1b776]:
{{{#!CommitTicketReference repository=""
revision="be1b776ad8d6f9bccfbdf63f84b16fb81a13119e"
Fixed #36213 -- Doc'd MySQL's handling of self-select updates in
QuerySet.update().

Co-authored-by: Andro Ranogajec <rano...@gmail.com>
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/36213#comment:15>

[Django]

#36213: Document that QuerySet.update can execute two separate SQL queries when
using MySQL
-------------------------------------+-------------------------------------
Reporter: Babak | Owner: Babak
Type: | Status: closed
Cleanup/optimization |
Component: Documentation | Version: dev
Severity: Normal | Resolution: fixed
Keywords: mysql self-select | Triage Stage: Ready for
race-condition | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Sarah Boyce <42296566+sarahboyce@…>):

In [changeset:"c68f3516be266cbc3929e51e32ed63d304ac5cc6" c68f3516]:
{{{#!CommitTicketReference repository=""
revision="c68f3516be266cbc3929e51e32ed63d304ac5cc6"
[5.2.x] Fixed #36213 -- Doc'd MySQL's handling of self-select updates in

QuerySet.update().

Co-authored-by: Andro Ranogajec <rano...@gmail.com>

Backport of be1b776ad8d6f9bccfbdf63f84b16fb81a13119e from main.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/36213#comment:16>

[Django]

#36213: Document that QuerySet.update can execute two separate SQL queries when
using MySQL
-------------------------------------+-------------------------------------
Reporter: Babak | Owner: Babak
Type: | Status: closed
Cleanup/optimization |
Component: Documentation | Version: dev
Severity: Normal | Resolution: fixed
Keywords: mysql self-select | Triage Stage: Ready for
race-condition | checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Sarah Boyce <42296566+sarahboyce@…>):

In [changeset:"b3b09dc6ce72f2aa778b95dc988653bf8c034035" b3b09dc6]:
{{{#!CommitTicketReference repository=""
revision="b3b09dc6ce72f2aa778b95dc988653bf8c034035"
[5.1.x] Fixed #36213 -- Doc'd MySQL's handling of self-select updates in

QuerySet.update().

Co-authored-by: Andro Ranogajec <rano...@gmail.com>

Backport of be1b776ad8d6f9bccfbdf63f84b16fb81a13119e from main.
}}}
--

Ticket URL: <https://code.djangoproject.com/ticket/36213#comment:17>