Aggregate annotation, Case() - When(): AssertionError No exception message supplied

[Lukas Klement]

Aggregating annotations works for simple Sum, Count, etc. operations, but fails when the Sum() contains a Case() When() operation.

To reproduce the issue, a simplified scenario:
Suppose you have recipes (e.g. Pasta dough, Tomato Sauce, etc.), which are linked to dishes (e.g. Pasta with tomato sauce) and ingredients (e.g. Flour, Water, Tomatoes). Ingredients are shared across all users, but each user can add a different purchase price to an ingredient (-> cost). We want to sum up the cost of a dish (i.e. summing up the cost of all ingredients across all recipes of a dish).

models.py

class Ingredient(models.Model):
    pass

class IngredientDetail(models.Model):
    ingredient = models.ForeignKey(Ingredient, on_delete=models.CASCADE, related_name='ingredient_details')
    user = models.ForeignKey(User, limit_choices_to={'role': 0}, on_delete=models.CASCADE)
    cost = models.DecimalField(blank=True, null=True, decimal_places=6, max_digits=13)

class Recipe(models.Model):
    dishes = models.ManyToManyField(Dish, related_name='dishes_recipes', through='RecipeDish')
    ingredients = models.ManyToManyField(Ingredient, related_name='ingredients_recipes', through='RecipeIngredient')

class RecipeDish(models.Model):
    recipe = models.ForeignKey(Recipe, on_delete=models.CASCADE, related_name='recipedish_recipe')
    dish = models.ForeignKey(Dish, on_delete=models.CASCADE, related_name='recipedish_dish')


class RecipeIngredient(models.Model):
    recipe = models.ForeignKey(Recipe, on_delete=models.CASCADE, related_name='recipeingredient_recipe')
    ingredient = models.ForeignKey(Ingredient, on_delete=models.CASCADE, related_name='recipeingredient_ingredient')
    quantity = models.DecimalField(blank=True, null=True, decimal_places=2, max_digits=6)

Different users can attach different IngredientDetail objects to a shared Ingredient objects (one-to-many relationship). The objective is to calculate how many ingredients added to a recipe (many-to-many relationship through RecipeIngredient) don't have a cost value set. To achieve this without using .extra(), we annotate the appropriate cost field to IngredientNutrition, then sum up the missing values using a Sum(), Case() and When() aggregation.

def aggregate_nutrition_data(recipes, user):
    annotations['cost_value'] = Subquery(
        IngredientDetail.objects.filter(Q(ingredient_id=OuterRef('ingredient_id')) & Q(user=user)).values('cost')[:1]
    )
    ingredients = RecipeIngredient.objects.filter(recipe__in=recipes).annotate(**annotations)
    aggregators['cost_missing'] = Coalesce(Sum(
        Case(
           When(**{'cost_value': None}, then=Value(1)),
           default=Value(0),
           output_field=IntegerField()
       )
    ), Value(0))
    data = ingredients.aggregate(**aggregators)

    return data

Expected behaviour: a dictionary is returned: {'cost_missing': <value>}, containing as a value the number of missing cost values.Actual behaviour: AssertionError No exception message supplied is returned on the line data = ingredients.aggregate(aggregators) (see abbreviated stacktrace below).

When the aggregated field name is set to a field that exists on the model of the queryset (i.e. is not annotated), the aggregation works. For example: instead of using cost_value=None in the When() of the aggregators, using quantity=None works.
Similarly, doing an aggregation over an annotated field without using Case() and When() works. For example:

    aggregators['cost'] = Coalesce(
        Sum(
            F('cost_value') * F('quantity') / F('cost_quantity')
            output_field=DecimalField()
        ), Value(0)

---

Stacktrace:

<Line with the .aggregate(...)>
        return query.get_aggregation(self.db, kwargs) ...
/Users/{{path}}/lib/python3.6/site-packages/django/db/models/sql/query.py in get_aggregation
                    expression, col_cnt = inner_query.rewrite_cols(expression, col_cnt) ...
/Users/{{path}}/lib/python3.6/site-packages/django/db/models/sql/query.py in rewrite_cols
                new_expr, col_cnt = self.rewrite_cols(expr, col_cnt) ...
/Users/{{path}}/lib/python3.6/site-packages/django/db/models/sql/query.py in rewrite_cols
                new_expr, col_cnt = self.rewrite_cols(expr, col_cnt) ...
/Users/{{path}}/lib/python3.6/site-packages/django/db/models/sql/query.py in rewrite_cols
                new_expr, col_cnt = self.rewrite_cols(expr, col_cnt) ...
/Users/{{path}}/lib/python3.6/site-packages/django/db/models/sql/query.py in rewrite_cols
                new_expr, col_cnt = self.rewrite_cols(expr, col_cnt) ...
/Users/{{path}}/lib/python3.6/site-packages/django/db/models/sql/query.py in rewrite_cols
                new_expr, col_cnt = self.rewrite_cols(expr, col_cnt) ...
/Users/{{path}}/lib/python3.6/site-packages/django/db/models/sql/query.py in rewrite_cols
                new_expr, col_cnt = self.rewrite_cols(expr, col_cnt) ...
/Users/{{path}}/lib/python3.6/site-packages/django/db/models/sql/query.py in rewrite_cols
        annotation.set_source_expressions(new_exprs) ...
/Users/{{path}}/lib/python3.6/site-packages/django/db/models/expressions.py in set_source_expressions
        assert not exprs 

The database is a PostgreSQL database, the Django version is 2.1.7, the Python version is 3.6.4. The problem also exists under Django version 2.2b1

[Simon Charette]

Hey Lukas,

Given the assertion error it looks like there's something wrong here but because of the limited data you provided it's almost impossible for volunteers to reproduce and confirm Django is actually at fault.

Please re-open this ticket if you can provide a reduced test case for your problem. This should include a set of minimal models calls to build the queryset that crashes on .aggregate. It would also be great if you could test against the current Django 2.2 beta (2.2b1) and confirm whether or not it's fixed there.

Thank you for your understanding.

[Lukas Klement]

Hi Simon, thank you for your response.

I have re-opened the ticket and added a simplified example to reproduce the issue. The issue is current in Django 2.1 and can be reproduced under Django version 2.2b1 as well.