I gave up on Mysql long time ago when I found out that it cannot refer to temporary tables multiple times in the same query. Not sure if it has been fixed now...

EDIT: Does not seem fixed https://dev.mysql.com/doc/refman/8.0/en/temporary-table-prob...