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 in 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?

[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]

[David Sanders]

Looks like the query in the description was wrong, I've updated it.

In your example PREMANAND, you aren't using the annotation in the UPDATE. You have the annotation named bname and you don't use that in the UPDATE. I realize that's because my original query was wrong, but pointing out why you didn't see the issue.

Using the two models in the description, here's a minimal reproduce case including creating objects:

>>> bar = Bar.objects.create(name="Test Name")
>>> Foo.objects.create(related_bar=bar, bar_name="Replace Me")
>>> Foo.objects.annotate(related_bar_name=F('related_bar__name')).update(bar_name=F('related_bar_name'))

Note that the annotation name is used as the update value. If you look at the SQL that is generated that you provided, you can see that on MySQL it breaks it into two queries, one SELECT then an UPDATE. Unless it carries the result from the SELECT over to the UPDATE that isn't going to work. In my testing the UPDATE tries to use the annotation name directly, which fails because it is an unknown column name in the UPDATE query.

Replying to 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]

[Paolo Melchiorre]

This bug is still present for me with Django 2.0 and PostgreSQL 9.6.

I solved using Subquery in a way similar as suggested in this my StackOverflow answer: ​https://stackoverflow.com/a/50134728/755343

[Mariusz Felisiak]

We decided to return error message in such cases, so I'm closing this as a duplicate of #28408.