Opened 9 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 )
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 , 9 years ago
Triage Stage: | Unreviewed → Accepted |
---|
comment:2 by , 8 years ago
Owner: | changed from | to
---|---|
Status: | new → assigned |
comment:4 by , 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.
follow-up: 7 comment:5 by , 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]
comment:6 by , 8 years ago
Description: | modified (diff) |
---|
comment:7 by , 8 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 , 7 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 , 5 years ago
Resolution: | → duplicate |
---|---|
Status: | assigned → closed |
Summary: | Using Annotation As Update Parameter Generates Invalid SQL → Using 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.
I ran against the dev version but the queries generated are something different.
models.py
If you notice the sql that is generated is inner join and not outer join as you mentioned.
Can you please clarify on it and provide a working code on it.?