#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 django-updates+unsubscr...@googlegroups.com.
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.
