Some findings/ideas on possible expression field support:
The SQL standard requires, that within a single UPDATE command the
column ordering does not matter. This effectively means, that the RHS of
the SET clause operates on old record values, while LHS gets the new
values, which dont manifest before the whole command returned.*
This allows a more aggressive transformation of updating fields with
expression values - those field updates could be pulled upfront and
executed separately, before applying changes from literal values in the
VALUES table. But since the expression values might be randomly
scattered across all to-be-updated fields, slicing into
expression/literal value fields at individual objects would lead to tons
of VALUES tables with different dimensions + extra field ref updates
(again creating high query load).
Suggestion:
A more straight forward way excludes objects with expression values from
the VALUES table handling, and updates them upfront with the fallback
path. This should still be an equivalent transformation in set theory,
given that expressions cannot directly ref a different row. Is that
always the case? (Otherwise we have a bigger problem with row order
dependency during an update...)
Compared to the individual object/field splitting, this way has several
advantages:
- easier to comprehend (at least for me)
- avoids building CASE chains in the VALUES table update command (keeps
the fast path fast)
- much easier to test (as branching happens in two main blocks, and not
deep at single object-update level creating a deep control-flow
branching hell)
- automatically deals with the MySQL update issue the same way as the
current bulk_update implementation
While I think that this should produce the same update results as the
current bulk_update implementation, I may have overlooked crucial
details, that need further to be addressed. Esp. around multi-table
inheritance the correct field update ordering is not yet clear to me -
Is this always done as second update after the local fields? How does
the ascent into parent fields work here? Is this python-mro ordered?
Cheers,
jerch
[*] MySQL/MariaDB have a non-standard UPDATE implementation in this
regard, as can be tested in this fiddle:
https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=5e596bc7d256ad85a5fd4718acd46496
(works for all other db engines...)
Should this be noted in the .update docs?
--
You received this message because you are subscribed to the Google Groups "Django
developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-developers+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-developers/efa48bac-ae4e-5aaa-c65b-922a116959ee%40netzkolchose.de.
