Union Operator merges annotated values wrongfully

[Tobias Maschek]

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?

[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 ​docs), so in your case it creates:

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 ​conditional aggregation, e.g.

Employee.objects.annotate(total=Sum('shift__dur', filter=Q(shift__start__range=(monday, monday + timedelta(weeks=1))))) 

[Tobias Maschek]

Replying to 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 ​docs) [...]

Thank you for the quick clarification!