Bug #65869 Column names are cached by a SP that creates a tempory table
Submitted: 11 Jul 2012 2:39 Modified: 11 Jul 2012 4:26
Reporter: Adrian Cornish Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.5.20-log OS:Linux
Assigned to: CPU Architecture:Any
Tags: Optimizer, prepared statements

[11 Jul 2012 2:39] Adrian Cornish
Description:
This is contrived example that shows the problem - real issue was a more complex SP

MySql is caching column names from a SELECT * statement with a SP that recreates a temp table for reporting purposes.

How to repeat:
-- Create need tables
DROP TABLE IF EXISTS foo;
CREATE TABLE foo(
col1 int,
col2 int);
INSERT INTO foo VALUES(1,2),(3,4),(5,6);

-- Create SP
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 > 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 ;

-- Run the SP to create V1 of the table
CALL mysp(0);

-- Drop the temp table
DROP TABLE mydata;

-- Run the SP again with new options
CALL mysp(1);
ERROR 1054 (42S22): Unknown column 'qlgqp1.mydata.col1' in 'field list'

Suggested fix:
When dropping a table inside or outside a SP all cached results related to the table should also be dropped.
[11 Jul 2012 2:41] Adrian Cornish
Developer comment should point to this link http://stackoverflow.com/questions/11370602/why-is-mysql-caching-the-column-names-of-a-tem...
[11 Jul 2012 4:17] Valeriy Kravchuk
This is a duplicate of Bug #61434.
[11 Jul 2012 4:26] Adrian Cornish
Thanks Valeriy Kravchuk  - agree it sounds like the same issue - please deal with this as appropriate within procedure here