Opened 2 years ago

Last modified 3 months ago

#26539 assigned Bug

Using Annotation As Update Parameter Generates Invalid SQL

Reported by: David Sanders Owned by: PREMANAND
Component: Database layer (models, ORM) Version: master
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 (8)

comment:1 Changed 2 years ago by Tim Graham

Triage Stage: UnreviewedAccepted

comment:2 Changed 2 years ago by PREMANAND

Owner: changed from nobody to PREMANAND
Status: newassigned

comment:3 Changed 23 months ago by 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.?

Last edited 23 months ago by PREMANAND (previous) (diff)

comment:4 Changed 23 months ago by 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.

comment:5 Changed 23 months ago by 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]
Last edited 23 months ago by PREMANAND (previous) (diff)

comment:6 Changed 18 months ago by David Sanders

Description: modified (diff)

comment:7 in reply to:  5 Changed 18 months ago by 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]

comment:8 Changed 3 months ago by 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

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