Description:
I previously posted this on the forums, and it was brought to my attention that feature requests can also be made here...
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.
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.
How to repeat:
Create a temporary table and refer to it in a CTE and try referencing it multiple times...
WITH parameters AS (SELECT * FROM parameters)
...
where parameters is a temporary table
Suggested fix:
Allow CTE aliases of temporary tables to be referenced multiple times, if possible...
Allowing temporary tables to be referenced multiple times in same query would be even better, but I believe this has already been suggested...
