[Fixed]-Weird behavior in Django queryset union of values

1👍

✅

I should mention that I can fix that by annotating the queryset again and coalescing the null values to 0, like this:

everyone.\
    annotate(real_points=Concat(Coalesce(F('points'), 0), Value(''))).\
    values('pk', 'real_points')

<QuerySet [{'pk': 2, 'real_points': 190}, {'pk': 3, 'real_points': 150}, {'pk': 4, 'real_points': 0}]>

But I wish to understand why the union does not work as I expected in my original question.

EDIT:
I think I got it. A friend instructed me to use django-debug-toolbar to check my SQL queries to investigate further on this situation and I found out the following:

Since it’s a union of two queries, the second query annotation is somehow not considered and the COALESCE to 0 is not used. By moving that to the first query it is propagated to the second query and I could achieve the expected result.

Basically, I changed the following:

# Moved the "Coalesce" to the initial query
people_with_points = Person.objects.\
    filter(answer__correct=True).\
    annotate(points=Coalesce(Sum('answer__points'), 0)).\
    values('pk', 'points')

# Second query does not have it anymore
people_without_points = Person.objects.\
    exclude(pk__in=people_with_points.values_list('pk')).\
    values('pk', 'points')

# We will have the values with 0 here!
everyone = people_with_points | people_without_points

Leave a comment