id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
32043	OuterRef with possible NULL generates value = NULL instead of value IS NULL in COUNT() group by.	Illia Petrov	nobody	"Let's imagine we have two models(db - PostgreSQL):
{{{
class Category(models.Model):
     name = models.CharField(max_length=255)

class Tag(models.Model):
    PRIORITY_CHOICES = (
       (1, ""1""),
       (2, ""2""),
       (3, ""3""),
    )
    category = models.ForeignKey(Category, on_delete=models.SET_NULL, null=True, blank=True)
    priority = models.PositiveSmallIntegerField(default=PRIORITY_CHOICES[0][0], choices=PRIORITY_CHOICES)
}}}

Then we want to get all tags for category_id=3 or category_id=None with the number of tags with priority=3 grouped by categories(remember that we have null=True in the category field, so category_id could be empty):

{{{
from django.db.models import OuterRef, Subquery, Q, Count
import pprint

q = Q(category_id=3)|Q(category_id=None)
queryset = Tag.objects.filter(q, priority=3)
subquery = Tag.objects.filter(
     priority=OuterRef('priority'), 
     category_id=OuterRef('category_id')
).values('priority').annotate(priority_count=Count('*'))

result = queryset.annotate(
     priority_count=Subquery(subquery.values('priority_count'), output_field=IntegerField())
).values('id', 'category_id', 'priority', 'priority_count')
pprint.pprint(result)
}}}

Output result:

{{{
<QuerySet [{'id': 28, 'category_id': None, 'priority': 3, 'priority_count': None}, {'id': 39, 'category_id': 3, 'priority': 3, 'priority_count': 3}, {'id': 40, 'category_id': 3, 'priority': 3, 'priority_count': 3}, {'id': 41, 'category_id': 3, 'priority': 3, 'priority_count': 3}]>
}}}

Result SQL query:

{{{
SELECT ""tag"".""id"",
       ""tag"".""category_id"",
       ""tag"".""priority"",
       (SELECT COUNT(*) AS ""priority_count""
        FROM ""tag"" U0
        WHERE (U0.""priority"" = (""tag"".""priority"") AND
               U0.""category_id"" = (""tag"".""category_id""))
        GROUP BY U0.""priority"") AS ""priority_count""
FROM ""tag""
WHERE ((""tag"".""category_id"" = 3 OR ""tag"".""category_id"" IS NULL) AND
        ""tag"".""priority"" = 3)
}}}

Problem:
In output we can see priority_count == None for category_id == None.
The reason is clear, since in subquery we have U0.""category_id"" = (""tag"".""category_id""))
So for now we ended up using UNION for separate queries with category_id=None and category_id=3.

Question:
Is it possible to do it without a union using Django ORM?
Will it be fixed in future?"	Bug	closed	Database layer (models, ORM)	3.2	Normal	needsinfo	OuterRef, ORM, SQL, Subquery		Unreviewed	0	0	0	0	0	0