[Django] #25136: Multiple annotations result in wrong results with MySQL backend
Django
----------------------------------------------+--------------------
Reporter: mdomans | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: 1.8
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
The problem is the difference between the results of **count()** on
**entries** relation and **num_entries** if I also want an annotation for
**alerts** relation
{{{#!python
reports = Report.objects.filter(
user=request.user).annotate(num_entries=Count('entries'),
num_alerts=Count('alerts'))
print report.num_entries, report.entries.count()
}}}
Yet, this problem did not happen if there was only 1 argument to annotate.
What's happening? Well, both **entries** and **alerts** are FK relations,
thus they result in this two left outer joins. The SQL is something like
this:
{{{#!sql
SELECT `coverage_reports`.*
COUNT(`alerts_alert`.`id`) AS `num_alerts`,
COUNT(`coverage_reports_entry`.`id`) AS `num_entries`
FROM `coverage_reports_coveragereport`
LEFT OUTER JOIN `alerts_alert` ON ( `coverage_reports_coveragereport`.`id`
= `alerts_alert`.`coverage_report_id` )
LEFT OUTER JOIN `coverage_reports_entry` ON
(`coverage_reports_coveragereport`.`id` =
`coverage_reports_entry`.`coverage_report_id` )
WHERE `coverage_reports_coveragereport`.`user_id` = 1 GROUP BY
`coverage_reports_coveragereport`.`id` ;
}}}
This is problematic as joins will duplicate parent records if more than
one child record is associated to it. This is what can inflate values from
aggregate functions.
The fix to this particular query was such:
{{{#!sql
SELECT reports.id, alerts.num_alerts, entries.num_entries
FROM coverage_reports_coveragereport AS reports
LEFT JOIN
(SELECT coverage_report_id, COUNT(*) AS num_alerts FROM alerts_alert
GROUP BY coverage_report_id) AS alerts
ON reports.id = alerts.coverage_report_id
LEFT JOIN
(SELECT coverage_report_id, COUNT(*) AS num_entries FROM
coverage_reports_entry GROUP BY coverage_report_id) AS entries
ON reports.id = entries.coverage_report_id
WHERE reports.user_id = 1 GROUP BY reports.id;
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/25136>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: mdomans | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
Comment:
Does adding
[https://docs.djangoproject.com/en/1.8/ref/models/querysets/#django.db.models.Count.distinct
distinct=True] to the the `Count`s resolve the issue? If so, we could
probably add an example to `topics/db/aggregation` about this.
--
Ticket URL: <https://code.djangoproject.com/ticket/25136#comment:1>
Django
Reporter: mdomans | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by mdomans):
Yes, it does though it's rather unobvious.
--
Ticket URL: <https://code.djangoproject.com/ticket/25136#comment:2>
Django
--------------------------------------+------------------------------------
Reporter: mdomans | Owner: nobody
Type: Cleanup/optimization | Status: new
Component: Documentation | Version: 1.8
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
--------------------------------------+------------------------------------
Changes (by timgraham):
* component: Database layer (models, ORM) => Documentation
* stage: Unreviewed => Accepted
* type: Bug => Cleanup/optimization
* easy: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/25136#comment:3>
Django
Reporter: mdomans | Owner:
Type: | caioariede
Cleanup/optimization | Status: assigned
Component: Documentation | Version: 1.8
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
Changes (by caioariede):
* owner: nobody => caioariede
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/25136#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: mdomans | Owner:
Type: | caioariede
Cleanup/optimization | Status: assigned
Component: Documentation | Version: 1.8
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by caioariede):
Pull request: https://github.com/django/django/pull/5085
--
Ticket URL: <https://code.djangoproject.com/ticket/25136#comment:5>
Django
-------------------------------------+-------------------------------------
Reporter: mdomans | Owner:
Type: | caioariede
Component: Documentation | Version: 1.8
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"3862c568ac1b920188ecfbe5cd8073160206d6b9" 3862c568]:
{{{
#!CommitTicketReference repository=""
revision="3862c568ac1b920188ecfbe5cd8073160206d6b9"
Fixed #25136 -- Documented Count('X', distinct=True) in aggregate topic
guide.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/25136#comment:6>
Django
-------------------------------------+-------------------------------------
Reporter: mdomans | Owner:
Type: | caioariede
Cleanup/optimization | Status: closed
Component: Documentation | Version: 1.8
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tim Graham <timograham@…>):
In [changeset:"9f10c5cdf5dbee111556f2b800c624f0202f51e6" 9f10c5c]:
{{{
#!CommitTicketReference repository=""
revision="9f10c5cdf5dbee111556f2b800c624f0202f51e6"
[1.8.x] Fixed #25136 -- Documented Count('X', distinct=True) in aggregate
topic guide.
Backport of 3862c568ac1b920188ecfbe5cd8073160206d6b9 from master
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/25136#comment:7>
Django
-------------------------------------+-------------------------------------
Reporter: mdomans | Owner:
Type: | caioariede
Cleanup/optimization | Status: closed
Component: Documentation | Version: 1.8
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tim Graham <timograham@…>):
In [changeset:"fe70f280d7ea4402f676696c4013c4a23d4e4990" fe70f280]:
{{{
#!CommitTicketReference repository=""
revision="fe70f280d7ea4402f676696c4013c4a23d4e4990"
Refs #25136 -- Fixed nonexistent field reference in aggregation topic
guide.
Thanks Ankush Thakur for the report and Simon for the review.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/25136#comment:8>
Django
-------------------------------------+-------------------------------------
Reporter: mdomans | Owner:
Type: | caioariede
Cleanup/optimization | Status: closed
Component: Documentation | Version: 1.8
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tim Graham <timograham@…>):
In [changeset:"7934695a0fcab621f7edb4accf0465c532f4d73d" 7934695a]:
{{{
#!CommitTicketReference repository=""
revision="7934695a0fcab621f7edb4accf0465c532f4d73d"
[1.8.x] Refs #25136 -- Fixed nonexistent field reference in aggregation
topic guide.
Thanks Ankush Thakur for the report and Simon for the review.
Backport of fe70f280d7ea4402f676696c4013c4a23d4e4990 from master
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/25136#comment:9>
Django
-------------------------------------+-------------------------------------
Reporter: mdomans | Owner:
Type: | caioariede
Cleanup/optimization | Status: closed
Component: Documentation | Version: 1.8
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tim Graham <timograham@…>):
In [changeset:"cc5c4ae35de2170fc0b597a9c6acb941b91941ab" cc5c4ae]:
{{{
#!CommitTicketReference repository=""
revision="cc5c4ae35de2170fc0b597a9c6acb941b91941ab"
[1.9.x] Refs #25136 -- Fixed nonexistent field reference in aggregation
topic guide.
Thanks Ankush Thakur for the report and Simon for the review.
Backport of fe70f280d7ea4402f676696c4013c4a23d4e4990 from master
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/25136#comment:10>