#34639: MySQL 8.0 hangs indefinitely when using the admin search with a Foreign
Key
and Annotate
--------------------------------+------------------------------------
Reporter: Nicolas Lupien | Owner: nobody
Type: Bug | Status: new
Component: contrib.admin | Version: 4.2
Severity: Normal | Resolution:
Keywords: mysql | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
--------------------------------+------------------------------------
Comment (by Simon Charette):
Looking at the generated SQL in more details it seems like the annotation
is properly stripped but not the now unnecessary join that was required
for it
{{{#!sql
SELECT
`myapp_organization`.`id`,
`myapp_organization`.`name`,
COUNT(`myapp_member`.`id`) AS `member__count`
FROM
`myapp_organization`
LEFT OUTER JOIN `myapp_member` ON (
`myapp_organization`.`id` = `myapp_member`.`organization_id`
)
WHERE
EXISTS(
SELECT
1 AS `a`
FROM
`myapp_organization` U0
-- U1 is a completely unused alias now that COUNT(`U1`.`id`)
is stripped!
LEFT OUTER JOIN `myapp_member` U1 ON (U0.`id` =
U1.`organization_id`)
LEFT OUTER JOIN `myapp_member` U2 ON (U0.`id` =
U2.`organization_id`)
WHERE
(
(
U0.`name` LIKE '%a%'
OR U2.`name` LIKE '%a%'
)
AND U0.`id` = (`myapp_organization`.`id`)
)
-- Ditto with GROUP BY since aggregation is discarded
GROUP BY
U0.`id`
ORDER BY
NULL
LIMIT
1
)
GROUP BY
`myapp_organization`.`id`
ORDER BY
`myapp_organization`.`name` ASC,
`myapp_organization`.`id` DESC;
}}}
That's something the ORM is not good at.
Since `annotate`, `filter` and friends are ''additive only'' (there's no
API to discard of them) the ORM was never optimized to have an operation
that strips unused JOINs when the select mask changes or filters are
elided (e.g. they end up raising `EmptyQuerySet`). This was identified
when working on stripping unused annotations on aggregations in #28477
(59bea9efd2768102fc9d3aedda469502c218e9b7) and is relatively hard to
solve.
What's interesting here is that prior to
187118203197801c6cb72dc8b06b714b23b6dd3d the reporter was likely not even
getting the right results when searching because they were running into
#2361 (notice the double left join against the multi-valued `myapp_member`
table) which means their `member__count` returned the wrong values.
--
Ticket URL: <https://code.djangoproject.com/ticket/34639#comment:8>
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/01070188a3d2a6e4-68e302f2-4d02-4e2c-8c64-5b495f245702-000000%40eu-central-1.amazonses.com.
