[Django] #28811: KeyError when using a regular annotation inside an F-statement in a group by annotation
Django
by annotation
-------------------------------------+-------------------------------------
Reporter: Robin | Owner: nobody
Ramael |
Type: Bug | Status: new
Component: Database | Version: 1.11
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
When a queryset is first annotated in the regular way (just adding a
column), and then that annotation is used inside an F-expression in a
group by-annotation, the query generation crashes with a KeyError because
it can't find the first annotation.
Code to reproduce:
== models.py:
{{{
from django.db import models
class Data(models.Model):
option = models.CharField(max_length=2)
value = models.FloatField()
}}}
== tests.py:
{{{
from django.db.models import F, Sum, Value
from django.test import TestCase
from .models import Data, Thing
class AnnotateTests(TestCase):
def test_simple(self):
Data.objects.create(option='A', value=1)
Data.objects.create(option='A', value=2)
Data.objects.create(option='B', value=3)
Data.objects.create(option='B', value=4)
data_qs = (Data.objects
.annotate(multiplier=Value(3)) # will of course be
far more complex in the wild
# group by option => sum of value * multiplier
.values('option')
.annotate(multiplied_value_sum=Sum(F('multiplier') *
F('value')))
.order_by())
print(list(data_qs))
}}}
There is a workaround, though: replacing the F-expression in the second
with the value of the first doesn't require the annotation lookup,
avoiding the KeyError, so the solution for this might just be a better
error message, although this gets unwieldy for complex annotations.
Running this test will result in the following traceback:
{{{
Creating test database for alias 'default'...
System check identified no issues (0 silenced).
E
======================================================================
ERROR: test_simple (annotate.tests.AnnotateTests)
----------------------------------------------------------------------
Traceback (most recent call last):
File "/Users/robin/src/ormweirdness/annotate/tests.py", line 21, in
test_simple
.annotate(multiplied_value_sum=Sum(F('multiplier') * F('value')))
File "/Users/robin/.virtualenvs/ormweirdness/lib/python3.6/site-
packages/django/db/models/query.py", line 945, in annotate
clone.query.add_annotation(annotation, alias, is_summary=False)
File "/Users/robin/.virtualenvs/ormweirdness/lib/python3.6/site-
packages/django/db/models/sql/query.py", line 973, in add_annotation
summarize=is_summary)
File "/Users/robin/.virtualenvs/ormweirdness/lib/python3.6/site-
packages/django/db/models/aggregates.py", line 19, in resolve_expression
c = super(Aggregate, self).resolve_expression(query, allow_joins,
reuse, summarize)
File "/Users/robin/.virtualenvs/ormweirdness/lib/python3.6/site-
packages/django/db/models/expressions.py", line 548, in resolve_expression
c.source_expressions[pos] = arg.resolve_expression(query, allow_joins,
reuse, summarize, for_save)
File "/Users/robin/.virtualenvs/ormweirdness/lib/python3.6/site-
packages/django/db/models/expressions.py", line 411, in resolve_expression
c.lhs = c.lhs.resolve_expression(query, allow_joins, reuse, summarize,
for_save)
File "/Users/robin/.virtualenvs/ormweirdness/lib/python3.6/site-
packages/django/db/models/expressions.py", line 471, in resolve_expression
return query.resolve_ref(self.name, allow_joins, reuse, summarize)
File "/Users/robin/.virtualenvs/ormweirdness/lib/python3.6/site-
packages/django/db/models/sql/query.py", line 1472, in resolve_ref
return self.annotation_select[name]
KeyError: 'multiplier'
----------------------------------------------------------------------
Ran 1 test in 0.004s
FAILED (errors=1)
Destroying test database for alias 'default'...
}}}
I've found one person that also seemed to have this problem on the django-
users mailing list: https://groups.google.com/forum/#!topic/django-
users/SYAGaVuEjHY
Happens on Django 1.11.7, python3.6. sqlite3 as well as postgres10.
--
Ticket URL: <https://code.djangoproject.com/ticket/28811>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
by annotation
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 1.11
(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
-------------------------------------+-------------------------------------
* stage: Unreviewed => Accepted
--
Ticket URL: <https://code.djangoproject.com/ticket/28811#comment:1>
Django
by annotation
-------------------------------------+-------------------------------------
| Ramael
Type: Bug | Status: assigned
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: nobody => Robin Ramael
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/28811#comment:2>
Django
by annotation
-------------------------------------+-------------------------------------
| Ramael
Type: Bug | Status: assigned
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* Attachment "patchfor28811.diff" added.
patch
Django
by annotation
-------------------------------------+-------------------------------------
| Ramael
Type: Bug | Status: assigned
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* Attachment "patchfor28811.diff" added.
patch
--
Django
by annotation
-------------------------------------+-------------------------------------
| Ramael
Type: Bug | Status: assigned
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* has_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/28811#comment:3>
Django
by annotation
-------------------------------------+-------------------------------------
| Ramael
Type: Bug | Status: assigned
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_better_patch: 0 => 1
Comment:
Are you able to send a pull request? Please reuse an existing model rather
than adding a new one. Perhaps using `Publisher` in `tests/annotations`
would be fine.
--
Ticket URL: <https://code.djangoproject.com/ticket/28811#comment:4>
Django
by annotation
-------------------------------------+-------------------------------------
| Ramael
Type: Bug | Status: assigned
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Robin Ramael):
Opened a PR with the requested changes here:
https://github.com/django/django/pull/9528/
--
Ticket URL: <https://code.djangoproject.com/ticket/28811#comment:5>
Django
by annotation
-------------------------------------+-------------------------------------
| Ramael
Component: Database layer | Version: 1.11
(models, ORM) |
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"fbf647287ebd9898bff69c65a89fa09a903adaa5" fbf64728]:
{{{
#!CommitTicketReference repository=""
revision="fbf647287ebd9898bff69c65a89fa09a903adaa5"
Fixed #28811 -- Fixed crash when combining regular and group by
annotations.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28811#comment:6>
Django
by annotation
-------------------------------------+-------------------------------------
| Ramael
Type: Bug | Status: closed
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Robin Ramael):
This bug is also present in 1.11. Any chance it might be merged into that
branch as well?
--
Ticket URL: <https://code.djangoproject.com/ticket/28811#comment:7>
Django
by annotation
-------------------------------------+-------------------------------------
| Ramael
Type: Bug | Status: closed
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tim Graham):
Not unless it's a regression (see our
[https://docs.djangoproject.com/en/dev/internals/release-process
/#supported-versions supported versions policy]).
--
Ticket URL: <https://code.djangoproject.com/ticket/28811#comment:8>