#26539: Using Annotation As Update Parameter Generates Invalid SQL
----------------------------------------------+--------------------
Reporter: dsanders11 | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: master
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
If you try to use an annotation which spans tables in an update,
everything will resolve and attempt to execute but the SQL will fail
because for MySQL the query is broken into a `SELECT` and then an
`UPDATE`, but the necessary info is not available in the UPDATE. On
sqlite3 it generates an `UPDATE` that looks similar but with the `SELECT`
as a subquery.
Example:
{{{
class Bar(models.Model):
name = models.CharField(max_length=32)
class Foo(models.Model):
related_bar = models.ForeignKey(Bar)
bar_name = models.CharField(max_length=32)
Foo.objects.annotate(bar_name=F('related_bar__name')).update(name=F('bar_name'))
}}}
On MySQL produces:
{{{
SELECT `test_foo`.`id`, `test_bar`.`name` AS `bar_name` FROM `test_foo`
LEFT OUTER JOIN `test_bar` ON ( `test_foo`.`related_bar` = `test_bar`.`id`
);
UPDATE `test_foo` SET `bar_name` = `test_bar`.`name` WHERE `test_foo`.`id`
IN (1);
}}}
I don't think there's a trivial fix for this. Doing an `UPDATE` with a
`JOIN` appears to have different syntax in the various implementations and
isn't possible at all on sqlite3, so it would need to be multiple queries.
Seems that until it can be made to work correctly an error should occur if
attempted, similar to the
[https://github.com/django/django/blob/master/django/db/models/sql/compiler.py#L1098
error for aggregates] in an `UPDATE`.
--
Ticket URL: <https://code.djangoproject.com/ticket/26539>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/053.4470fe623e7973ee1158e0e04b332f1d%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
