Opened 7 years ago

Closed 5 years ago

#28289 closed Bug (fixed)

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

Reported by: Karolis Ryselis Owned by: Can Sarıgöl
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: yes 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 7 years ago.

Download all attachments as: .zip

Change History (8)

comment:1 by Tim Graham, 7 years ago

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.

by Tim Graham, 7 years ago

Attachment: 28289-test.diff added

comment:2 by Can Sarıgöl, 5 years ago

Has patch: set
Owner: changed from nobody to Can Sarıgöl
Status: newassigned
Version: 1.11master

Hi, PR
To finding the inherited columns, thought two approaches:
a- try to parse the columns from raw sql with using a convert function which returns a list from sql string. (with regex pattern maybe)
b- if the query contains a column that comes from the inherited model, take this field name with get_fields() - get_fields(include_parents=False)

I chose b.
I hoped to find a property that contains whether the model is inherited. Is this possible?
Because of that, it performs every time.

comment:3 by Mariusz Felisiak, 5 years ago

Patch needs improvement: set

comment:4 by Can Sarıgöl, 5 years ago

Patch needs improvement: unset

comment:5 by Mariusz Felisiak, 5 years ago

Patch needs improvement: set

comment:6 by Can Sarıgöl, 5 years ago

Patch needs improvement: unset

comment:7 by Mariusz Felisiak <felisiak.mariusz@…>, 5 years ago

Resolution: fixed
Status: assignedclosed

In 52545e7:

Fixed #28289 -- Fixed crash of RawSQL annotations on inherited model fields.

Note: See TracTickets for help on using tickets.
Back to Top