Re: Optimizer being overly aggressive

[Rick James]

Interesting.
A guess... The "SELECT * FROM mydata" is "lazily" compiled, but then then it is remembered in the SP. That is, the first time, it decides that "*" is "col1", and it saves that. The second call fails to re-"compile" that statement.

Plan A: Write a bug report (bugs.mysql.com)

Plan B: PREPARE & EXECUTE the SELECT *

Plan C: Use an alias to avoid the problem:
SET @s=CONCAT('CREATE TEMPORARY TABLE mydata AS SELECT ', @mycol, ' AS the_col FROM foo');
...
SELECT the_col FROM mydata;

Plan D: Use a subquery rather than an explicit temp table.