Opened 8 years ago

Closed 5 years ago

#26539 closed Bug (duplicate)

Using Annotation As Update Parameter Generates Invalid SQL.

Reported by: David Sanders Owned by: PREMANAND
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by 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.

Change History (9)

comment:1 by Tim Graham, 8 years ago

Triage Stage: UnreviewedAccepted

comment:2 by PREMANAND, 8 years ago

Owner: changed from nobody to PREMANAND
Status: newassigned

comment:3 by PREMANAND, 8 years ago

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

Last edited 8 years ago by PREMANAND (previous) (diff)

comment:4 by Tim Graham, 8 years ago

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.

comment:5 by PREMANAND, 8 years ago

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]
Last edited 8 years ago by PREMANAND (previous) (diff)

comment:6 by David Sanders, 7 years ago

Description: modified (diff)

in reply to:  5 comment:7 by David Sanders, 7 years ago

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]

comment:8 by Paolo Melchiorre, 6 years ago

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

comment:9 by Mariusz Felisiak, 5 years ago

Resolution: duplicate
Status: assignedclosed
Summary: Using Annotation As Update Parameter Generates Invalid SQLUsing Annotation As Update Parameter Generates Invalid SQL.

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

Note: See TracTickets for help on using tickets.
Back to Top