#30188: Aggregate annotation, Case() - When(): AssertionError No exception
message
supplied
-------------------------------------+-------------------------------------
Reporter: Lukas Klement | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 2.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: query, aggregate, | Triage Stage:
case/when | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by Lukas Klement:
Old description:
> 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)
> cost_unit = models.CharField(choices=UNITS, max_length=15,
> default='kg')
>
> 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
New description:
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
--
--
Ticket URL: <https://code.djangoproject.com/ticket/30188#comment:6>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/070.b816e8839faec218eea1f9bf1a233ffc%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
