title: Why is mysql caching the column names of a temporay table that is deleted?
body: <p>I've reduced my issue down to this simple SP. The column names are getting cached in the SELECT * at the end. I have no idea why or how to stop it. I tried adding SQL_NO_CACHE but that makes no difference.</p>
  
  <pre><code>DROP TABLE IF EXISTS foo;
  CREATE TABLE foo(
  col1 int,
  col2 int);
  INSERT INTO foo VALUES(1,2),(3,4),(5,6);
  DROP PROCEDURE IF EXISTS mysp;
  DELIMITER ;;
  CREATE DEFINER=root@localhost PROCEDURE mysp(c INT)
  BEGIN
     DROP TABLE IF EXISTS mydata;
  
     SET @mycol='col1';
  
     IF c &gt; 0 THEN SET @mycol:='col2';
     END IF;
  
     SET @s=CONCAT('CREATE TEMPORARY TABLE mydata AS SELECT ', @mycol, ' FROM foo');
     PREPARE stmt FROM @s;
     EXECUTE stmt;
     DEALLOCATE PREPARE stmt;
  
  -- The following select call fails on 2nd and subsequent executions of the SP
     SELECT SQL_NO_CACHE * FROM mydata;
     SELECT "Please see new temp table mydata" as Result;
  END ;;
  DELIMITER ;
  </code></pre>
  
  <p>Version </p>
  
  <pre><code>mysql&gt; SELECT VERSION();
  +------------+
  | VERSION()  |
  +------------+
  | 5.5.15-log |
  +------------+
  1 row in set (0.00 sec)
  </code></pre>
  
  <p>First run works fine as expected</p>
  
  <pre><code>mysql&gt; CALL mysp(0);
  +------+
  | col1 |
  +------+
  |    1 |
  |    3 |
  |    5 |
  +------+
  3 rows in set (0.17 sec)
  
  +----------------------------------+
  | Result                           |
  +----------------------------------+
  | Please see new temp table mydata |
  +----------------------------------+
  1 row in set (0.17 sec)
  
  Query OK, 0 rows affected (0.17 sec)
  </code></pre>
  
  <p>Now if I try and run it again using the other column</p>
  
  <pre><code>mysql&gt; CALL mysp(1);
  ERROR 1054 (42S22): Unknown column 'qlgqp1.mydata.col1' in 'field list'
  mysql&gt; SELECT @mycol;
  +--------+
  | @mycol |
  +--------+
  | col2   |
  +--------+
  1 row in set (0.00 sec)
  </code></pre>
  
  <p>If I recreate the storedprocedure again its works</p>
  
  <pre><code>mysql&gt; CALL mysp(1);
  +------+
  | col2 |
  +------+
  |    2 |
  |    4 |
  |    6 |
  +------+
  3 rows in set (0.18 sec)
  
  +----------------------------------+
  | Result                           |
  +----------------------------------+
  | Please see new temp table mydata |
  +----------------------------------+
  1 row in set (0.18 sec)
  
  Query OK, 0 rows affected (0.18 sec)
  </code></pre>
  
  <p>But if I try switching back to the first column - even if I try dropping the temp table first - it still doesn't work</p>
  
  <pre><code>mysql&gt; CALL mysp(0);
  ERROR 1054 (42S22): Unknown column 'qlgqp1.mydata.col2' in 'field list'
  mysql&gt; DROP TABLE mydata;
  Query OK, 0 rows affected (0.03 sec)
  
  mysql&gt; CALL mysp(0);
  ERROR 1054 (42S22): Unknown column 'qlgqp1.mydata.col2' in 'field list'
  mysql&gt;
  </code></pre>
  
  <p>*<em>Additional info asked for by eggyal. Also I tried this on another mysql version with same result. *</em> </p>
  
  <pre><code>mysql&gt; CALL mysp(1);
  +------+
  | col2 |
  +------+
  |    2 |
  |    4 |
  |    6 |
  +------+
  3 rows in set (0.20 sec)
  
  +----------------------------------+
  | Result                           |
  +----------------------------------+
  | Please see new temp table mydata |
  +----------------------------------+
  1 row in set (0.20 sec)
  
  Query OK, 0 rows affected (0.20 sec)
  
  mysql&gt; describe mydata;
  +-------+---------+------+-----+---------+-------+
  | Field | Type    | Null | Key | Default | Extra |
  +-------+---------+------+-----+---------+-------+
  | col2  | int(11) | YES  |     | NULL    |       |
  +-------+---------+------+-----+---------+-------+
  1 row in set (0.00 sec)
  
  mysql&gt; CALL mysp(0);
  ERROR 1054 (42S22): Unknown column 'test.mydata.col2' in 'field list'
  mysql&gt; describe mydata;
  +-------+---------+------+-----+---------+-------+
  | Field | Type    | Null | Key | Default | Extra |
  +-------+---------+------+-----+---------+-------+
  | col1  | int(11) | YES  |     | NULL    |       |
  +-------+---------+------+-----+---------+-------+
  1 row in set (0.00 sec)
  </code></pre>
  
  <p>Interesting development of a fix  - changing the last few lines to a prepared statement works - but using exactly the same query as before.</p>
  
  <pre><code>-- The following select call fails on 2nd and subsequent executions of the SP
     PREPARE stmt FROM 'SELECT SQL_NO_CACHE * FROM mydata';
     EXECUTE stmt;
     DEALLOCATE PREPARE stmt;
     SELECT "Please see new temp table mydata" as Result;
  </code></pre>