subject:"\[Django\] #30188\: Aggregate annotation, Case\(\) \- When\(\)\: AssertionError No exception message supplied"

#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:  Accepted
  case/when  |
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  1
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Can Sarıgöl):

 Thanks for that. This is a better solution.

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/070.69dd15b5c360089ca8cf943dbb6d3923%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30188: Aggregate annotation, Case() - When(): AssertionError No exception message supplied

#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:  Accepted
  case/when  |
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  1
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Simon Charette):

 I just pushed another commit that addresses the double aliasing issue. I
 credited you in the commit message Can.

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/070.31681289d9236b1f81bdf5ba5c09159e%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30188: Aggregate annotation, Case() - When(): AssertionError No exception message supplied

#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:  Accepted
  case/when  |
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  1
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Simon Charette):

 Can, I guess we could somehow but I feel like we'd need a more elegant
 solution than that.

 Note that the double annotation issue doesn't only happen for subqueries,
 you might have noticed that the `company_id` which is first annotated as
 `Col0` gets annotated as `__col0` as well. This makes me believe we might
 need to do it for `Col` instances as well?

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/070.c93ceb044c4c4a5191f3461dfec94acc%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30188: Aggregate annotation, Case() - When(): AssertionError No exception message supplied

#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:  Accepted
  case/when  |
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  1
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Can Sarıgöl):

 Can we fix twice annotated problem in here? I thought that we can remove
 the expr if it is already in annotations ({{{expr == old_expr}}})


 {{{
 elif isinstance(expr, (Col, Subquery)) or (expr.contains_aggregate and not
 expr.is_summary):
  ...
  new_exprs.append(Ref(col_alias, expr))
  if isinstance(expr, Subquery):
  keys = {
  k for k, old_expr in self.annotations.items()
  if k.startswith('__col') is False and expr == old_expr
   }
  for _k in keys:
  del self.annotations[_k]
 }}}

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/070.690a153383c4ae8f16b0933b8de4f8f5%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30188: Aggregate annotation, Case() - When(): AssertionError No exception message supplied

Comment (by Simon Charette):

The assertion error is addressed by
https://github.com/django/django/pull/11062 but it unveiled another issue
regarding aggregation over annotations.

I managed to get it to work by adjusting the ''hacky''
`Query.rewrite_cols` function but then I noticed the subquery was
annotated twice which might hurt performance. I guess the suggested
approach at least works and the double annotation issue seems to be
generalized anyway. I suggest we open a following ticket to address the
`rewrite_cols` extra annotation issue and proceed with the current
approach for now.

--
Ticket URL:
Django
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/070.2007064e5dda40b3b80db1aecff8342b%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30188: Aggregate annotation, Case() - When(): AssertionError No exception message supplied

#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:  Accepted
  case/when  |
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  1
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Tim Graham):

 * needs_better_patch:  0 => 1


-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/070.4b79084e45eb5566d42c0c5b48552918%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30188: Aggregate annotation, Case() - When(): AssertionError No exception message supplied

2019-03-04 Thread Django

#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:  Accepted
  case/when  |
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Simon Charette):

 * cc: Simon Charette (added)


-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/070.734dbb451b76ec31288d80f17de1ba56%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30188: Aggregate annotation, Case() - When(): AssertionError No exception message supplied

#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:  Accepted
  case/when  |
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Can Sarıgöl):

 Query like this:

 {{{
 SELECT COALESCE(
 SUM(CASE WHEN (SELECT U0."cost"
FROM "t30188_ingredientdetail"
 U0
WHERE U0."ingredient_id" =
 (subquery."ingredient_id")  LIMIT 1) IS NULL THEN 1 ELSE 1 END), 1)
 FROM (SELECT "t30188_recipeingredient"."id" AS Col1,
   (SELECT U0."cost"
FROM "t30188_ingredientdetail" U0
WHERE U0."ingredient_id" =
 ("t30188_recipeingredient"."ingredient_id")  LIMIT 1) AS "cost_value",
   "U0"."ingredient_id" AS "__col1"
   FROM "t30188_recipeingredient"
   GROUP BY "t30188_recipeingredient"."id",
   (SELECT U0."cost"
FROM "t30188_ingredientdetail" U0
WHERE U0."ingredient_id" =
 ("t30188_recipeingredient"."ingredient_id")  LIMIT 1),
   "U0"."ingredient_id") subquery
 }}}
 the problem is {{{"U0"."ingredient_id" AS "__col1"}}}, It should have been
 {{{"t30188_recipeingredient"."ingredient_id"}}}. to solve this, I will
 also check your point @Simon Charette thanks.

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/070.c86fe689ee500e1413152c382e07ddf5%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30188: Aggregate annotation, Case() - When(): AssertionError No exception message supplied

Comment (by Simon Charette):

I agree with Lukas that we should investigate why the ORM crashes here as
it's a completely valid query from my perspective.

The fundamental issue here is that `Subquery` breaks the `Expression` API
by having a `get_source_expressions` method that returns `N > 0`
expressions while having a `set_source_expressions` that accepts none.

I happen to have a local branch I've been playing with locally to make
`Subquery` more a of dumb wrapper around `sql.Query` that happens to
restore this contract. I haven't tried it out against this exact set of
models but the fact the caller

[https://github.com/django/django/blob/b435f82939edf70674856e0e1cd63973c2e0a1d1/django/db/models/sql/query.py#L362-L389
hosts a FIXME] makes me feel like there might be something else going on.

--
Ticket URL:
Django
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/070.3fb2f8adcae430f6996f5a58bd402ce2%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30188: Aggregate annotation, Case() - When(): AssertionError No exception message supplied

#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:  Accepted
  case/when  |
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Can Sarıgöl):

 * cc: Can Sarıgöl (added)


Comment:

 Hi, actually I thought this sample is the same with below so the same
 exception message might be ok. Besides, I will check the query.


 {{{
 ingredients = RecipeIngredient.objects.aggregate(
 cost_missing = Coalesce(Sum(
 Case(
 When(ingredient__cost__isnull=False, then=Value(1)),
 default=Value(0),
 output_field=IntegerField())
 ), Value(0))
 )
 }}}

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/070.4f0bdae6597f9507e5d580dc1c9572b2%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30188: Aggregate annotation, Case() - When(): AssertionError No exception message supplied

#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:  Accepted
  case/when  |
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Tim Graham):

 I didn't look at this in any depth. I don't know whether or not the query
 is valid. I only meant to say that if it's not valid, there should be a
 better message. The proposed wording on the PR (if the query is indeed
 invalid) ... 'Related Field got invalid lookup: salary' ... doesn't seem
 to provide much explanation.

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/070.d61309d2df9f7f3053440849bd21b090%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30188: Aggregate annotation, Case() - When(): AssertionError No exception message supplied

#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:  Accepted
  case/when  |
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-

Comment (by Lukas Klement):

 Hi Tim, thank you for attaching an example project - why is the query not
 valid? Why is it that annotated fields derived from annotated fields that
 used Case() / When() cannot be aggregated?

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/070.02ba5c990402f5f4cf656ab2dc8cd05f%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30188: Aggregate annotation, Case() - When(): AssertionError No exception message supplied

#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:  Accepted
  case/when  |
Has patch:  1|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Can Sarıgöl):

 * has_patch:  0 => 1


Comment:

 [https://github.com/django/django/pull/11050 PR]

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/070.edaa7da835b90f1187bc06962c878b33%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30188: Aggregate annotation, Case() - When(): AssertionError No exception message supplied

2019-02-20 Thread Django

#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:  Accepted
  case/when  |
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Tim Graham):

 * Attachment "t30188.zip" added.


-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/070.bc372a296a36551f2d33dc6f75869179%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30188: Aggregate annotation, Case() - When(): AssertionError No exception message supplied

2019-02-20 Thread Django

#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:  Accepted
  case/when  |
Has patch:  0|  Needs documentation:  0
  Needs tests:  0|  Patch needs improvement:  0
Easy pickings:  0|UI/UX:  0
-+-
Changes (by Tim Graham):

 * stage:  Unreviewed => Accepted


Comment:

 Sample project attached. Even if the query isn't valid, a more helpful
 error message would be useful.

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/070.b19d56cb632d8d8c7cd80e0e9aff7407%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30188: Aggregate annotation, Case() - When(): AssertionError No exception message supplied

#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
-+-

Comment (by Carlton Gibson):

 Hi Lukas, Any chance you could put this into a sample project and attach
 that (or a test case in a PR) so we can run this example and experiment?
 (It would save a cycle and speed review if you could.)

 Thanks.

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/070.6a11879b1252e814a058c4a8dc14443d%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30188: Aggregate annotation, Case() - When(): AssertionError No exception message supplied

#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)
>
> 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': },
> 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:**
> {{{
> 
> return query.get_aggregation(self.db, kwargs) ...
> /Users/{{path}}/lib/python3.6/site-packages/django/db/models/sql/query.py
> in get_aggregation
>

Re: [Django] #30188: Aggregate annotation, Case() - When(): AssertionError No exception message supplied

#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': },
> 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:**
> {{{
> 
> return query.get_aggregation(self.db, kwargs) ...
>

Re: [Django] #30188: Aggregate annotation, Case() - When(): AssertionError No exception message supplied

#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': },
> 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:**
> {{{
> 
> return query.get_aggregation(self.db, kwargs) ...
>

Re: [Django] #30188: Aggregate annotation, Case() - When(): AssertionError No exception message supplied

#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
-+-

Comment (by Lukas Klement):

 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.

-- 
Ticket URL: 
Django 
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/070.3b77eed983b96d8ce32f7d46b6af9d5a%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #30188: Aggregate annotation, Case() - When(): AssertionError No exception message supplied