Opened 16 months ago

Closed 16 months ago

Last modified 15 months ago

#34464 closed Bug (fixed)

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

Reported by: Ian Cubitt Owned by: Simon Charette
Component: Database layer (models, ORM) Version: 4.2
Severity: Release blocker Keywords: postgresql
Cc: charette Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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

Attachments (1)

Screenshot 2023-04-06 at 09.30.15.png (229.5 KB ) - added by Ian Cubitt 16 months ago.
Reproducing the issue using my sample code in the ticket

Download all attachments as: .zip

Change History (10)

by Ian Cubitt, 16 months ago

Reproducing the issue using my sample code in the ticket

comment:1 by Mariusz Felisiak, 16 months ago

Cc: charette added
Severity: NormalRelease blocker

Thanks for the report!

Regression in 59bea9efd2768102fc9d3aedda469502c218e9b7.
Reproduced at 38e63c9e61152682f3ff982c85a73793ab6d3267.

comment:2 by Mariusz Felisiak, 16 months ago

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

comment:3 by Mariusz Felisiak, 16 months ago

Triage Stage: UnreviewedAccepted

comment:4 by Simon Charette, 16 months ago

Owner: changed from nobody to Simon Charette
Status: newassigned

comment:5 by Simon Charette, 16 months ago

Has patch: set

comment:6 by Mariusz Felisiak, 16 months ago

Triage Stage: AcceptedReady for checkin

comment:7 by Mariusz Felisiak <felisiak.mariusz@…>, 16 months ago

Resolution: fixed
Status: assignedclosed

In 9daf8b41:

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

Regression in 59bea9efd2768102fc9d3aedda469502c218e9b7.

Refs #28477.

Thanks Ian Cubitt for the report.

comment:8 by Mariusz Felisiak <felisiak.mariusz@…>, 16 months ago

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

comment:9 by Alexandr Artemyev, 15 months ago

Thanks for the fix. 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.

Last edited 15 months ago by Alexandr Artemyev (previous) (diff)
Note: See TracTickets for help on using tickets.
Back to Top