[Django] #26539: Using Annotation As Update Parameter Generates Invalid SQL
Django
----------------------------------------------+--------------------
Reporter: dsanders11 | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: master
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
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(bar_name=F('related_bar__name')).update(name=F('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
[https://github.com/django/django/blob/master/django/db/models/sql/compiler.py#L1098
error for aggregates] in an `UPDATE`.
--
Ticket URL: <https://code.djangoproject.com/ticket/26539>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: dsanders11 | Owner: nobody
Type: Bug | Status: new
(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
Changes (by timgraham):
* needs_better_patch: => 0
* needs_docs: => 0
* needs_tests: => 0
* stage: Unreviewed => Accepted
--
Ticket URL: <https://code.djangoproject.com/ticket/26539#comment:1>
Django
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
Easy pickings: 0 | UI/UX: 0
Changes (by PREM1980):
* owner: nobody => PREM1980
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/26539#comment:2>
Django
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
Easy pickings: 0 | UI/UX: 0
Comment (by PREM1980):
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 in 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?
--
Ticket URL: <https://code.djangoproject.com/ticket/26539#comment:3>
Django
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
Easy pickings: 0 | UI/UX: 0
Comment (by timgraham):
The idea of this ticket is to fix (or provide a helpful error message if
the query is impossible) for
[https://github.com/django/django/blob/4bc6b939944183533ae74791d21282e613f63a96/tests/update/tests.py#L174-L176
an existing test that's commented out].
--
Ticket URL: <https://code.djangoproject.com/ticket/26539#comment:4>
Django
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
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
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
Easy pickings: 0 | UI/UX: 0
Description changed by David Sanders:
Old description:
New description:
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
[https://github.com/django/django/blob/master/django/db/models/sql/compiler.py#L1098
error for aggregates] in an `UPDATE`.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/26539#comment:6>
Django
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
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?
--
Ticket URL: <https://code.djangoproject.com/ticket/26539#comment:7>
Django
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
Easy pickings: 0 | UI/UX: 0
Comment (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
--
Ticket URL: <https://code.djangoproject.com/ticket/26539#comment:8>
Django
-------------------------------------+-------------------------------------
Reporter: David Sanders | Owner: PREMANAND
Component: Database layer | Version: master
(models, ORM) |
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by felixxm):
* status: assigned => closed
* resolution: => duplicate
Comment:
We decided to return error message in such cases, so I'm closing this as a
duplicate of #28408.
--
Ticket URL: <https://code.djangoproject.com/ticket/26539#comment:9>