#26539: Using Annotation As Update Parameter Generates Invalid SQL
-------------------------------------+-------------------------------------
Reporter: dsanders11 | Owner: PREM1980
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 PREM1980):
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 is select statement is applied directly as a subselect.
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:5>
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 [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/068.f020b96f4a9de40b572203f3094061ea%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
