Opened 3 years ago

Closed 3 years ago

Last modified 3 years ago

#33477 closed Bug (invalid)

Union Operator merges annotated values wrongfully

Reported by: Tobias Maschek Owned by: nobody
Component: Database layer (models, ORM) Version: 4.0
Severity: Normal Keywords: annotation, querysets, merging
Cc: Tobias Maschek Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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?

Change History (3)

comment:1 by Tobias Maschek, 3 years ago

Cc: Tobias Maschek added

comment:2 by Mariusz Felisiak, 3 years ago

Resolution: invalid
Status: newclosed

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))))) 

in reply to:  2 comment:3 by Tobias Maschek, 3 years ago

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!

Note: See TracTickets for help on using tickets.
Back to Top