#25136: Multiple annotations result in wrong results with MySQL backend
----------------------------------------------+--------------------
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.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/050.31a43e8ffeaba010e86e7a7bb9053dd6%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
