#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)
Change History (10)
by , 20 months ago
Attachment: | Screenshot 2023-04-06 at 09.30.15.png added |
---|
comment:1 by , 20 months ago
Cc: | added |
---|---|
Severity: | Normal → Release blocker |
Thanks for the report!
Regression in 59bea9efd2768102fc9d3aedda469502c218e9b7.
Reproduced at 38e63c9e61152682f3ff982c85a73793ab6d3267.
comment:2 by , 20 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 , 20 months ago
Triage Stage: | Unreviewed → Accepted |
---|
comment:4 by , 19 months ago
Owner: | changed from | to
---|---|
Status: | new → assigned |
comment:5 by , 19 months ago
Has patch: | set |
---|
comment:6 by , 19 months ago
Triage Stage: | Accepted → Ready for checkin |
---|
comment:9 by , 19 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.
Reproducing the issue using my sample code in the ticket