#28477: Strip unused annotations from count queries
-------------------------------------+-------------------------------------
Reporter: Tom Forbes | Owner: Simon
Type: | Charette
Cleanup/optimization | Status: closed
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Dustin Lorres):
Is there a way to opt out of this behavior from application code? After
upgrading from 3.2 to 4.2 some of our pagination stopped working correctly
and it was traced back to `.count()` not providing the correct results,
likely from the updates in this ticket.
Here is an example of the failure (using PostgreSQL JSON query):
{{{
>>> qs = MyModel.objects.annotate(table_element=Func("data",
Value("$.MyArray[*]"),function="jsonb_path_query",output_field=JSONField())).filter(pk=1)
>>> qs.count()
1
>>> len(qs)
2
}}}
This assumes a very simple model `MyModel` with a JSON field `data` that
is has an instance (pk of 1) that has something the data field set to:
{{{
{
"MyArray": [{"id": 1, "name": "test"}, {"id": 2, "name": "test2"}]
}
}}}
The issue is now the `count` is not factoring in the annotation which will
actually increase the number of rows returned in the queryset (for this
example due to the jsonb_path_query which returns a set). It is only
counting the number of rows of `MyModel` which due to the `pk` filter will
only have one row returned.
Is there anyway to force the count operation to expand the query and
include the annotation?
I tried to filter on the count of the jsonb path query, but it failed:
{{{
>>> qs = MyModel.objects.annotate(my_count=Count(Func("data",
Value("$.MyArray[*]"),function="jsonb_path_query",output_field=JSONField()))).filter(pk=1,
my_count__gt=0)
>>> qs.count()
Exception:
django.db.utils.NotSupportedError: set-returning functions are not allowed
in HAVING
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28477#comment:22>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/010701892897cac3-45b216c5-5644-4bf6-9611-48984d78e17e-000000%40eu-central-1.amazonses.com.
