Another subselect instead of join issue

Thank you for escalating this issue, despite initially marking it resolved.

I don’t know if any of the workarounds I have found will help you in the meantime, but I have tried to expand on it from my recent experiences.
Poorly rendered SQL resulting in extremely slow MySQL 8.0 queries