Opened 7 years ago
Last modified 2 years ago
#28616 assigned Bug
DISTINCT ON and update() does the wrong thing
Reported by: | Daniel Keller | Owned by: | Anvesh Mishra |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 1.11 |
Severity: | Normal | Keywords: | |
Cc: | Mariusz Felisiak, Anvesh Mishra | Triage Stage: | Accepted |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
If I have a model like
from django.db import models from django.utils import timezone class Foo(models.Model): num = models.IntegerField(default=0) date = models.DateTimeField(default=timezone.now) flag = models.BooleanField(default=False)
and (with Postgresql) I do
Foo.objects.order_by('num', '-date').distinct('num').only('pk')
I get a query like
SELECT DISTINCT ON ("app_foo"."num") "app_foo"."id" AS Col1 FROM "app_foo" ORDER BY "app_foo"."num" ASC, "app_foo"."date" DESC; args=()
which returns the latest Foo
for each num
.
BUT, if I do
Foo.objects.order_by('num', '-date').distinct('num').update(flag=True)
then it executes
UPDATE "app_foo" SET "flag" = true; args=(True)
which updates everything.
I don't necessarily think that this behaviour should be supported, but it would be nice to at least get a NotImplementedError
.
Change History (6)
comment:1 by , 7 years ago
Cc: | added |
---|
comment:2 by , 7 years ago
Triage Stage: | Unreviewed → Accepted |
---|
comment:3 by , 2 years ago
Owner: | changed from | to
---|---|
Status: | new → assigned |
comment:4 by , 2 years ago
comment:5 by , 2 years ago
Cc: | added |
---|---|
Owner: | removed |
Status: | assigned → new |
comment:6 by , 2 years ago
Owner: | set to |
---|---|
Status: | new → assigned |
On looking into it further it seems like the problem comes because of how the QuerySet.update()
works
query = self.query.chain(sql.UpdateQuery)
The Query.chain()
changes the class of the current object to UpdateQuery
which removes the previous query as a new object is created i.e
SELECT DISTINCT ON ("distinct_foo"."num") "distinct_foo"."id", "distinct_foo"."num", "distinct_foo"."date", "distinct_foo"."flag" FROM "distinct_foo" ORDER BY "distinct_foo"."num" ASC, "distinct_foo"."date" DESC
and then replaces it with
UPDATE "app_foo" SET "flag" = true; args=(True)
instead of combining both.
Should we add a support for this behavior or stick to
NotImplementedError
?