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
35586	Aggregation optimization doesn't account for not referenced set-returning annotations on Postgres	Devin Cox	Devin Cox	"There is a bug where the queryset count is inaccurate when doing an annotation with JSONB_PATH_QUERY. The count is fixed after the queryset is evaluated.  

I discovered this bug via `PageNumberPagination` from `rest_framework.pagination`, specifically in the function `paginate_queryset`. This Paginator takes in an object_list (in this case a Queryset) and determines the length by first checking if a .count() method is available, otherwise by doing len(object_list). In this case, it is determining length through Queryset.count().

When the Queryset is annotated, and the length of the results increases, the count remains stale--only returning the original count. 

Currently I have a workaround by adding a DISTINCT, INTERSECTION, UNION, or DIFFERENCE to the queryset. Unfortunately, I now also need to have ordering which makes this tricky to continue working around.

I believe this was a regression back in 4.2. This was previously asked about here: https://code.djangoproject.com/ticket/28477#comment:22, and this thread was referred: https://forum.djangoproject.com/t/django-4-2-behavior-change-when-using-arrayagg-on-unnested-arrayfield-postgresql-specific/21547. However, the `Unnest` approach does not work for our use case.

Here are steps to reproduce:

**models.py:
**

{{{
class TestModel(models.Model):
    test_json = models.JSONField(default=dict, blank=True)
    id = models.IntegerField(primary_key=True)
}}}

**tests.py**

{{{
from .models import TestModel
from django.contrib.postgres.fields import JSONField
from django.db.models import Func, Value

    def test_bug(self):
        test_model = TestModel.objects.create(
            test_json={
                ""test_key"" : [
                    {
                        ""id"" : 1,
                        ""name"" : ""test1""
                    },
                    {
                        ""id"" : 2,
                        ""name"" : ""test2""
                    }
                ]
            },
            id=1
        )
        test_model.save()
        qs = TestModel.objects.annotate(
            table_element=Func(
                ""test_json"",
                Value(""$.test_key[*]""),
                function=""jsonb_path_query"",
                output_field=JSONField(),
                subquery=True
            )
        ).filter(pk=1)

        # qs.count() and len(qs) should be equal, but currently they are not. Running qs.count() after len(qs) is currently equal because the queryset was evaluated.

        self.assertEqual(qs.count(), len(qs))

}}}

Thank you for any guidance and/or support!"	Bug	closed	Database layer (models, ORM)	5.0	Normal	fixed	postgres, set-returning, aggregation, annotation	Simon Charette David Sanders Jacob Walls	Ready for checkin	1	0	0	0	0	0