[Django] #34464: QuerySet.count() regression on Django 4.2 with values() and annotate()
Django
-------------------------------------+-------------------------------------
Reporter: Ian | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 4.2
layer (models, ORM) |
Severity: Normal | Keywords: postgresql
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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:
{{{
#!python
class SampleModel(models.Model):
sample_datetime_field = models.DateTimeField()
}}}
Example code to reproduce the issue:
{{{
#!python
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
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34464>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Reporter: Ian | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: postgresql | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* Attachment "Screenshot 2023-04-06 at 09.30.15.png" added.
Reproducing the issue using my sample code in the ticket
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 4.2
(models, ORM) |
Keywords: postgresql | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: charette (added)
* severity: Normal => Release blocker
Comment:
Thanks for the report!
Regression in 59bea9efd2768102fc9d3aedda469502c218e9b7.
Reproduced at 38e63c9e61152682f3ff982c85a73793ab6d3267.
--
Ticket URL: <https://code.djangoproject.com/ticket/34464#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: Ian Cubitt | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: postgresql | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mariusz Felisiak):
Django 4.1:
{{{#!sql
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:
{{{#!sql
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
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34464#comment:2>
Django
-------------------------------------+-------------------------------------
Reporter: Ian Cubitt | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Release blocker | Resolution:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Unreviewed => Accepted
--
Ticket URL: <https://code.djangoproject.com/ticket/34464#comment:3>
Django
-------------------------------------+-------------------------------------
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: postgresql | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: nobody => Simon Charette
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/34464#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: Ian Cubitt | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: postgresql | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Simon Charette):
* has_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/34464#comment:5>
Django
-------------------------------------+-------------------------------------
Reporter: Ian Cubitt | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Release blocker | Resolution:
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/34464#comment:6>
Django
-------------------------------------+-------------------------------------
Reporter: Ian Cubitt | Owner: Simon
| Charette
Component: Database layer | Version: 4.2
(models, ORM) |
Keywords: postgresql | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"9daf8b4109c3e133eb57349bb44d73cc60c5773c" 9daf8b41]:
{{{
#!CommitTicketReference repository=""
revision="9daf8b4109c3e133eb57349bb44d73cc60c5773c"
Fixed #34464 -- Fixed queryset aggregation over group by reference.
Regression in 59bea9efd2768102fc9d3aedda469502c218e9b7.
Refs #28477.
Thanks Ian Cubitt for the report.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34464#comment:7>
Django
-------------------------------------+-------------------------------------
Reporter: Ian Cubitt | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: postgresql | 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 Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"511dc3db539122577aaba71f5a24d65d5adab092" 511dc3db]:
{{{
#!CommitTicketReference repository=""
revision="511dc3db539122577aaba71f5a24d65d5adab092"
[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
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34464#comment:8>
Django
-------------------------------------+-------------------------------------
Reporter: Ian Cubitt | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: postgresql | 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 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/34464#comment:9>