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

[Karolis Ryselis]

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

[Tim Graham]

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

[Can Sarıgöl]

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.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 52545e7:

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