CTE referencing temporary table

[Thejaka Maldeniya]

I don't believe this is mentioned in the docs, but when a CTE references a temporary table such as WITH parameters AS (SELECT * FROM parameters), it still doesn't work if multiple references are made to parameters. I was hoping to use this as a workaround for the inability to reopen temp tables, but this did not work. I had to use temporary tables, as the MySQL optimizer doesn't index temporary result sets of subqueries or CTEs, so my query wasn't performing well. With temp tables with indexes, the performance can be greatly improved. However, due to the limitation with reopening temp tables, I had to use non-temp (ordinary permanent tables), but this also has caveats, as since this set of queries is invoked based on user request to generate a report, multiple concurrent invocations are impossible. A CTE or some other solution is required as multiple references to the table are being made. Any suggestions?

I don't know where to post a feature request, but I was hoping this could somehow be fixed in a newer version of MySQL (a minor version/revision please, as I'm using on RDS and major version upgrades require downtime, and are difficult to coordinate and arrange). I know the reopening temp tables feature has already been requested, but I was hoping at least the CTE workaround could be implemented.

Thanks for your attention.