[Django] #33477: Union Operator merges annotated values wrongfully
Django
-------------------------------------+-------------------------------------
Reporter: Tobias | Owner: nobody
Maschek |
Type: Bug | Status: new
Component: Database | Version: 4.0
layer (models, ORM) | Keywords: annotation,
Severity: Normal | querysets, merging
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Hi, maybe I'm doing something majorly incorrectly, but Django merges
Querysets at least in an unexpected way.
= Setup
I have the following models:
{{{
class Employee(models.Model):
name = models.CharField(max_length=64)
class Shift(models.Model):
employee = models.ForeignKey(Employee, on_delete=models.CASCADE)
start = models.DateTimeField()
dur = models.DecimalField(max_digits=4, decimal_places=2, default=0)
}}}
= Querysets
With
{{{ employees_with_shifts =
Employee.objects.filter(shift__start__range=(monday, monday +
timedelta(weeks=1))).annotate(total=Sum('shift__dur')) }}}
I can get all employees, that have at least one shift in the week
containing the given monday, with the annotated total worktime.
With
{{{
employees_without_shifts = Employee.objects.annotate(total=Value(0,
IntegerField()))
.exclude(shift__start__range=(monday, monday + timedelta(weeks=1)))
}}}
I get all employees without any shifts and on all objects is the total
annotated with 0. So far, so good.
= The Problem
But if I'm now combining these two sets to `employees =
employees_with_shifts | employees_without_shifts`, **the annotated total
value is not any more zero** for all employees in
`employees_without_shifts `, instead it's some sum of all shifts (ignoring
the week). Even when the exclude statement is removed, it changes nothing.
I suspect that Django combines these two Querysets before evaluating, and
in the combination process the range gets thrown off.
If I'm combining these two sets with `employees =
sorted(chain(employees_with, employees_without), key=lambda instance:
instance.id)` the annotated value is correct.
Or do I use the union operator incorrectly?
--
Ticket URL: <https://code.djangoproject.com/ticket/33477>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: annotation, | Triage Stage:
querysets, merging | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: Tobias Maschek (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/33477#comment:1>
Django
-------------------------------------+-------------------------------------
Component: Database layer | Version: 4.0
(models, ORM) |
Keywords: annotation, | Triage Stage:
querysets, merging | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => invalid
Comment:
> Or do I use the union operator incorrectly?
As far as I'm aware, yes. `|` combines two querysets using the SQL `OR`
operator (see
[https://docs.djangoproject.com/en/stable/ref/models/querysets/#or docs]),
so in your case it creates:
{{{#!python
Employee.objects.filter(
Q(shift__start__range=(monday, monday + timedelta(weeks=1)))
|
~Q(shift__start__range=(monday, monday + timedelta(weeks=1)))
).annotate(total=Sum('shift__dur'))
}}}
You can use `filter` to get a
[https://docs.djangoproject.com/en/stable/ref/models/conditional-
expressions/#conditional-aggregation conditional aggregation], e.g.
{{{#!python
Employee.objects.annotate(total=Sum('shift__dur',
filter=Q(shift__start__range=(monday, monday + timedelta(weeks=1)))))
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/33477#comment:2>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: closed
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: annotation, | Triage Stage:
querysets, merging | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tobias Maschek):
Replying to [comment:2 Mariusz Felisiak]:
> > Or do I use the union operator incorrectly?
>
> As far as I'm aware, yes. `|` combines two querysets using the SQL `OR`
operator (see
[https://docs.djangoproject.com/en/stable/ref/models/querysets/#or docs])
[...]
Thank you for the quick clarification!
--
Ticket URL: <https://code.djangoproject.com/ticket/33477#comment:3>