#28289: Queryset.count does not with work raw sql annotations on inherited model
fields
-------------------------------------+-------------------------------------
Reporter: Karolis | Owner: nobody
Ryselis |
Type: | Status: new
Uncategorized |
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 |
-------------------------------------+-------------------------------------
Consider these models
{{{
class BaseItem(models.Model):
title = models.CharField(max_length=32)
class Item(BaseItem):
pass
}}}
If I use a `RawSQL` annotation of `Item`'s queryset that includes one of
the fields defined in `BaseItem` and call `.count()` on annotated
queryset, it fails with the error:
`django.db.utils.OperationalError: (1054, "Unknown column 'title' in
'field list'")` (MySQL 5.7)
code to reproduce the bug with given models:
{{{
queryset = Item.objects.all()
queryset = queryset.annotate(title2=RawSQL("title", ()))
queryset.count() # crashes
}}}
I have tracked down what causes this bug. `Query.get_aggregation` method
drops `INNER JOIN` required to select the `title` field. Specifically,
this code drops it:
{{{
if not inner_query.distinct:
# If the inner query uses default select and it has some
# aggregate annotations, then we must make sure the inner
# query is grouped by the main model's primary key.
However,
# clearing the select clause can alter results if distinct
is
# used.
if inner_query.default_cols and has_existing_annotations:
inner_query.group_by =
[self.model._meta.pk.get_col(inner_query.get_initial_alias())]
inner_query.default_cols = False
}}}
Code is taken from Django 1.8 but 1.11 looks the same.
`default_cols` is set to `False` and the `INNER JOIN` is dropped. Quick
fix is to add a condition for setting `default_cols` to `False`:
{{{
if not inner_query.distinct:
# If the inner query uses default select and it has some
# aggregate annotations, then we must make sure the inner
# query is grouped by the main model's primary key.
However,
# clearing the select clause can alter results if distinct
is
# used.
if inner_query.default_cols and has_existing_annotations:
inner_query.group_by =
[self.model._meta.pk.get_col(inner_query.get_initial_alias())]
if not has_existing_annotations:
inner_query.default_cols = False
}}}
I don't know if it could be done in a nicer way. I was able to reproduce
this in 1.8.18 and 1.11.0
--
Ticket URL: <https://code.djangoproject.com/ticket/28289>
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/050.db04601467c9f43df67d99a0d205ce01%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
