Using Annotation As Update Parameter Generates Invalid SQL

[David Sanders]

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(related_bar_name=F('related_bar__name')).update(bar_name=F('related_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 ​error for aggregates in an UPDATE.

[PREMANAND]

I ran against the dev version but the queries generated are something different.

models.py

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)    

If you notice the sql that is generated is inner join and not outer join as you mentioned.

>>> Foo.objects.annotate(b_name=F('related_bar__name'))
SELECT `polls_foo`.`id`,
       `polls_foo`.`related_bar_id`,
       `polls_foo`.`bar_name`,
       `polls_bar`.`name` AS `b_name`
FROM `polls_foo`
INNER JOIN `polls_bar` ON (`polls_foo`.`related_bar_id` = `polls_bar`.`id`) LIMIT 21 [0.34ms]

Can you please clarify on it and provide a working code on it.?

[Tim Graham]

The idea of this ticket is to fix (or provide a helpful error message if the query is impossible) for ​an existing test that's commented out.

[PREMANAND]

Need more clarifications on this.

SQLLite and Mysql both passes the test. The queries generated are different(sqllite has SUBSELECT) but the result of the subselect is same as MYSQL.

Here are the 2 queries thats generated. In MYSQL the select statement is generated first and then the results are applied to the update but in sqllite the select statement is applied directly as a subselect. Not sure what is expected in this ticket?

MYSQL results:-

>>> Foo.objects.annotate(bname=F('related_bar__name')).update(bar_name=F('bar_name'))
SELECT `polls_foo`.`id`
FROM `polls_foo`
INNER JOIN `polls_bar` ON (`polls_foo`.`related_bar_id` = `polls_bar`.`id`) [3.62ms]
UPDATE `polls_foo`
SET `bar_name` = `polls_foo`.`bar_name`
WHERE `polls_foo`.`id` IN (6,7)

SQLLite:-

>>> Foo.objects.annotate(bname=F('related_bar__name'))
SELECT "polls_foo"."id",
       "polls_foo"."related_bar_id",
       "polls_foo"."bar_name",
       "polls_bar"."name" AS "bname"
FROM "polls_foo"
INNER JOIN "polls_bar" ON ("polls_foo"."related_bar_id" = "polls_bar"."id") LIMIT 21 [0.21ms]
<QuerySet [<Foo: Foo object>, <Foo: Foo object>]>
>>> Foo.objects.annotate(bname=F('related_bar__name')).update(bar_name=F('bar_name'))
BEGIN [0.03ms]
UPDATE "polls_foo"
SET "bar_name" = "polls_foo"."bar_name"
WHERE "polls_foo"."id" IN
    (SELECT U0."id" AS Col1
     FROM "polls_foo" U0
     INNER JOIN "polls_bar" U1 ON (U0."related_bar_id" = U1."id")) [0.29ms]
#                            7) [0.22ms]