#26539: Using Annotation As Update Parameter Generates Invalid SQL
-------------------------------------+-------------------------------------
Reporter: David Sanders | Owner: PREMANAND
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (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 [comment:5 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]
> }}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26539#comment:7>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/068.4481c55e3b86baf0db79fd94668553c8%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
