Opened 2 years ago

Closed 2 years ago

Last modified 2 years ago

#30462 closed Bug (fixed)

Conditional aggregation doesn't work with subquery annotations.

Reported by: asomethings Owned by: nobody
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords: aggregation, annotation, subquery
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Aggregating+Count works fine when filtering argument exists in model.
BUT if it doesn't exist in model it throws an AssertionError.

I'll make an reproducible case below.
models.py

class Highlight(models.Model):
   class Meta:
        managed = False
        db_table = 'test_highlight'
   content = models.CharField(max_length=100)
    lang = models.IntegerField(null=True, blank=True)

class Page(models.Model):
   class Meta:
        managed = False
        db_table = 'test_Page'
   image = models.CharField(max_length=100)
   index = models.IntegerField()
   image_size = models.FloatField(default=1)
   text_id = models.PositiveIntegerField(blank=True, null=True, help_text='Includes Highlight ID')

class Ebook(models.Model):
   class Meta:
        managed = False
        db_table = 'test_Book'
   title = models.CharField(max_length=100)
   page = models.ForeignKey(Page, related_name='book')

class Company(models.Model):
   class Meta:
        managed = False
        db_table = 'test_company'
   name = models.CharField(max_length=100)
   books = models.ManyToManyField(EBook, related_name='company')
   type = models.PositiveIntegerField(blank=True, null=True)

Our Mission is to get count of Specific Highlight Language Counts.
And below is query for it

Company.objects.annotate(lang=Subquery(Highlight.objects.filter(pk=OuterRef('books__page__text_id')).values('lang')).values('lang').filter(lang__isnull=False, lang__in=[1,2,3]).aggregate(highlight_count=Count('pk', distinct=True, filter=Q(lang=1))

:::Expected behaviour:::
A dictionary is returned: {'hightlight_count': <value>}, containing value the number where Highlight_lang is 1.

:::Actual behaviour:::
Returns Assertion Error in query. No Exception Message is supplied for the error.

Making a similiar query not using annotated field works like

Company.objects.annotate(lang=Subquery(Highlight.objects.filter(pk=OuterRef('books__page__text_id')).values('lang')).values('lang').filter(lang__isnull=False, lang__in=[1,2,3]).aggregate(page_count=Count('pk', distinct=True, filter=Q(type__isnull=False, type=3))

Stacktrace

~/{{ path }}lib/python3.7/site-packages/django/db/models/query.py in aggregate(self, *args, **kwargs)
    377             if not query.annotations[alias].contains_aggregate:
    378                 raise TypeError("%s is not an aggregate expression" % alias)
--> 379         return query.get_aggregation(self.db, kwargs)
    380 
    381     def count(self):

~/{{ path }}lib/python3.7/site-packages/django/db/models/sql/query.py in get_aggregation(self, using, added_aggregate_names)
    459             for alias, expression in list(inner_query.annotation_select.items()):
    460                 if expression.is_summary:
--> 461                     expression, col_cnt = inner_query.rewrite_cols(expression, col_cnt)
    462                     outer_query.annotations[alias] = expression.relabeled_clone(relabels)
    463                     del inner_query.annotations[alias]

~/{{ path }}lib/python3.7/site-packages/django/db/models/sql/query.py in rewrite_cols(self, annotation, col_cnt)
    389                 # copied from the else clause, but this condition must appear
    390                 # before the contains_aggregate/is_summary condition below.
--> 391                 new_expr, col_cnt = self.rewrite_cols(expr, col_cnt)
    392                 new_exprs.append(new_expr)
    393             elif isinstance(expr, Col) or (expr.contains_aggregate and not expr.is_summary):

~/{{ path }}lib/python3.7/site-packages/django/db/models/sql/query.py in rewrite_cols(self, annotation, col_cnt)
    389                 # copied from the else clause, but this condition must appear
    390                 # before the contains_aggregate/is_summary condition below.
--> 391                 new_expr, col_cnt = self.rewrite_cols(expr, col_cnt)
    392                 new_exprs.append(new_expr)
    393             elif isinstance(expr, Col) or (expr.contains_aggregate and not expr.is_summary):

~/{{ path }}lib/python3.7/site-packages/django/db/models/sql/query.py in rewrite_cols(self, annotation, col_cnt)
    402                 # Some other expression not referencing database values
    403                 # directly. Its subexpression might contain Cols.
--> 404                 new_expr, col_cnt = self.rewrite_cols(expr, col_cnt)
    405                 new_exprs.append(new_expr)
    406         annotation.set_source_expressions(new_exprs)

~/{{ path }}lib/python3.7/site-packages/django/db/models/sql/query.py in rewrite_cols(self, annotation, col_cnt)
    404                 new_expr, col_cnt = self.rewrite_cols(expr, col_cnt)
    405                 new_exprs.append(new_expr)
--> 406         annotation.set_source_expressions(new_exprs)
    407         return annotation, col_cnt
    408 

~/{{ path }}lib/python3.7/site-packages/django/db/models/expressions.py in set_source_expressions(self, exprs)
    172 
    173     def set_source_expressions(self, exprs):
--> 174         assert not exprs
    175 
    176     def _parse_expressions(self, *expressions):

AssertionError: 

Tested with three django versions. - 2.2.1 / 2.2 / 2.1
None of those worked
Python - 3.7.5

Change History (2)

comment:1 Changed 2 years ago by Mariusz Felisiak

Resolution: fixed
Status: newclosed
Summary: Aggregation with annotation(Subquery) throws AssertionError:Conditional aggregation doesn't work with subquery annotations.
Type: UncategorizedBug
Version: 2.2master

Thanks for the report. It has been fixed on master in 1ca825e4dc186da2b93292b5c848a3e5445968d7.

Related with #30188.

comment:2 Changed 2 years ago by GitHub <noreply@…>

In ef9f2eb6:

Refs #30462 -- Added test for conditional aggregation on annotated subquery.

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