QuerySet ".update(...)" silently turns into SELECT+UPDATE? (MySQL)

Django Internals ORM
7

Thanks for seeing this through @bbkfhq :person_bowing:

For anyone interested in preventing this problem from happening in the first place on MySQL and potentially modernizing the SQL used to perform updates involving related tables (and even possibly adding support for aggregation) I’ve demonstrated that it should not be too hard to adjust SQLUpdateCompiler to take advantage of the non-standardized UPDATE FROM extension supported on Postgres, SQLite, and MySQL in some flavor.

This branch includes three improvements

  1. The first one avoids the Python-level materialization problem on MySQL entirely by forcing a database level materialization. This is a strategy we used for deletes successfully and reduces the race condition significantly (or would entirely if FOR UPDATE was used).
  2. The second dip toes in a MySQL flavor of UPDATE FROM by demonstrating it can be done. We’d want a database agnostic solution here though that is based on a feature flag (e.g. supports_update_from that would be disabled on Oracle, and SQLite < 3.33).
  3. The third demonstrates how the MySQL flavor of UPDATE FROM (which supports updating many tables in a single statement which is pretty wild) can be used to support MTI updates in a single statement even when RETURNING isn’t supported (which is the case on MySQL).

I think 1. should be considered a strong contender for merging as-is because it addresses a technically avoidable foot-gun and 2. is a good basis for anyone interested in learning more about the ORM (3. remains more a PoC than anythings). I’d be happy to propose a feature request for 1. if it gets support here and sponsor / mentor a potential contributor with a reasonable experience with SQL to see 2. through.

2 Likes