[Django] #32043: OuterRef with possible NULL generates value = NULL instead of value IS NULL in COUNT() group by.
Django
in COUNT() group by.
-------------------------------------+-------------------------------------
Reporter: | Owner: nobody
idobrodushniy |
Type: | Status: new
Uncategorized |
Component: Database | Version: 2.0
layer (models, ORM) | Keywords: OuterRef, ORM, SQL,
Severity: Normal | Subquery
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Let's imagine we have two models:
{{{
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?
--
Ticket URL: <https://code.djangoproject.com/ticket/32043>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
in COUNT() group by.
-------------------------------------+-------------------------------------
Type: Uncategorized | Status: new
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: OuterRef, ORM, SQL, | Triage Stage:
Subquery | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
New description:
Let's imagine we have two models(db - PostgreSQL:
Output result:
Result SQL query:
--
--
Ticket URL: <https://code.djangoproject.com/ticket/32043#comment:1>
Django
in COUNT() group by.
-------------------------------------+-------------------------------------
Type: Uncategorized | Status: new
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: OuterRef, ORM, SQL, | Triage Stage:
Subquery | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
> Let's imagine we have two models(db - PostgreSQL:
New description:
Let's imagine we have two models(db - PostgreSQL):
Output result:
Result SQL query:
--
--
Ticket URL: <https://code.djangoproject.com/ticket/32043#comment:2>
Django
in COUNT() group by.
-------------------------------------+-------------------------------------
Type: Uncategorized | Status: closed
Component: Database layer | Version: 2.0
(models, ORM) |
Keywords: OuterRef, ORM, SQL, | Triage Stage:
Subquery | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => needsinfo
Comment:
Hello there, from what I can see the generated SQL matches what your
specifying through your queryset construction.
Could you provide an example of the SQL you're trying to generate, its
expected result, and how you're achieving it using unions?
It's hard to tell without these details but I ''think'' you can achieve
what you're after by annotating your inner queryset with
`OuterRef('category_id')` and changing the
`category_id=OuterRef('category_id')` criteria accordingly
{{{#!python
subquery = Tag.objects.annotate(
outer_category_id=OuterRef('category_id'),
).filter(
Q(category=OuterRef('category_id')) | Q(category=None,
outer_category_id=None),
priority=OuterRef('priority'),
).values('priority').annotate(priority_count=Count('*'))
}}}
If that's the case then I fear we cannot change the current behaviour
without causing massive backward incompatibilities.
For example `filter(nullable_field=F('nullable_field'))` doesn't result in
`WHERE nullable_field = nullable_field OR (nullable_field IS NULL and
nullable_field IS NULL)` so making this change only for resolved
`OuterRef` would make the current behaviour even more inconsistent. The
ORM does a relatively good job at abstracting the triple-boolean logic of
SQL when provided literal values (e.g. `None` handling in `exclude()`) but
it doesn't deal with nullable expressions for performance reasons (hard to
determine if complex expressions are nullable and `OR` clauses required
for `IS NULL` makes the job of the query planner harder).
--
Ticket URL: <https://code.djangoproject.com/ticket/32043#comment:3>
Django
in COUNT() group by.
-------------------------------------+-------------------------------------
Type: Uncategorized | Status: new
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Normal | Resolution:
Subquery | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: closed => new
* resolution: needsinfo =>
Comment:
Hi Simon, thanks for the idea, but it isn't working with Django 3.2.4,
filtering by annotated 'outer_category_id' in the subquery raises an error
{{{
AttributeError: 'ResolvedOuterRef' object has no attribute 'get_lookup'
}}}
category_id is an annotated value in the inner query in my case.
Probably this is a bug?
--
Ticket URL: <https://code.djangoproject.com/ticket/32043#comment:4>
Django
in COUNT() group by.
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: OuterRef, ORM, SQL, | Triage Stage:
Subquery | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* version: 2.0 => 3.2
* type: Uncategorized => Bug
--
Ticket URL: <https://code.djangoproject.com/ticket/32043#comment:5>
Django
in COUNT() group by.
-------------------------------------+-------------------------------------
Type: Bug | Status: closed
Component: Database layer | Version: 3.2
(models, ORM) |
Keywords: OuterRef, ORM, SQL, | Triage Stage:
Subquery | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => needsinfo
Comment:
Denis, this is a separate issue, see #31714.
--
Ticket URL: <https://code.djangoproject.com/ticket/32043#comment:6>