Opened 6 months ago

Last modified 6 months ago

#28289 new Bug

QuerySet.count() does not with work raw sql annotations on inherited model fields

Reported by: Karolis Ryselis Owned by: nobody
Component: Database layer (models, ORM) Version: 1.11
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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

Attachments (1)

28289-test.diff (918 bytes) - added by Tim Graham 6 months ago.

Download all attachments as: .zip

Change History (2)

comment:1 Changed 6 months ago by Tim Graham

Summary: Queryset.count does not with work raw sql annotations on inherited model fieldsQuerySet.count() does not with work raw sql annotations on inherited model fields
Triage Stage: UnreviewedAccepted
Type: UncategorizedBug

I'm attaching a test for Django's test suite that I wrote quickly to reproduce this.

Changed 6 months ago by Tim Graham

Attachment: 28289-test.diff added
Note: See TracTickets for help on using tickets.
Back to Top