QuerySet.count() regression on Django 4.2 with values() and annotate()

[Ian Cubitt]

After upgrading to Django 4.2 we noticed incorrect results on some queries we use in reporting.

We are using PostgreSQL as our database.
Using psycopg2 or the new psycopg 3 support does not change the result.

Sample model:

class SampleModel(models.Model):
    sample_datetime_field = models.DateTimeField()

Example code to reproduce the issue:

from bug_test.models import SampleModel
from django.db.models import Count
from django.db.models.functions import TruncHour
from django.utils import timezone

# Prepopulate 10 hours of sample data

now = timezone.now()

for i in range(10):
    field_value = now - timedelta(hours=1 + i)

    for i in range(5):
        SampleModel.objects.create(sample_datetime_field=field_value)

# Affected query example

qs = SampleModel.objects.all().annotate(hour=TruncHour('sample_datetime_field')).\
    values('hour').annotate(hour_count=Count('*')).filter(hour_count__gte=5)

print(f"Count: {qs.count()}")  # Incorrectly returns 0. Returns correct result on Django 4.1
print(f"Len: {len(qs)}")  # Returns correct result of 10

[Ian Cubitt]

Reproducing the issue using my sample code in the ticket

[Mariusz Felisiak]

Thanks for the report!

Regression in 59bea9efd2768102fc9d3aedda469502c218e9b7.
Reproduced at 38e63c9e61152682f3ff982c85a73793ab6d3267.

[Mariusz Felisiak]

Django 4.1:

SELECT COUNT(*) FROM (
    SELECT
        DATE_TRUNC('hour', "ticket_34464_samplemodel"."sample_datetime_field") AS "hour",
        COUNT(*) AS "hour_count"
    FROM "ticket_34464_samplemodel"
    GROUP BY
        DATE_TRUNC('hour', "ticket_34464_samplemodel"."sample_datetime_field")
    HAVING COUNT(*) >= 5
) subquery

Django 4.2:

SELECT COUNT(*) FROM (
    SELECT
        "ticket_34464_samplemodel"."id" AS "col1"
    FROM "ticket_34464_samplemodel"
    GROUP BY
        DATE_TRUNC('hour', "ticket_34464_samplemodel"."sample_datetime_field"),
        1
    HAVING COUNT(*) >= 5
) subquery

[Mariusz Felisiak <felisiak.mariusz@…>]

In 9daf8b41:

Fixed #34464 -- Fixed queryset aggregation over group by reference.

Regression in 59bea9efd2768102fc9d3aedda469502c218e9b7.

Refs #28477.

Thanks Ian Cubitt for the report.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 511dc3db:

[4.2.x] Fixed #34464 -- Fixed queryset aggregation over group by reference.

Regression in 59bea9efd2768102fc9d3aedda469502c218e9b7.

Refs #28477.

Thanks Ian Cubitt for the report.

Backport of 9daf8b4109c3e133eb57349bb44d73cc60c5773c from main

[Alexandr Artemyev]

Thanks for the correction. Spent hours trying to figure out what was going on before finding this ticket.
Can confirm that stable/4.2.x works in my case now.
I look forward to the release.