MySQL Bugs: #65869: Column names are cached by a SP that creates a tempory table

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

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.

Developer comment should point to this link http://stackoverflow.com/questions/11370602/why-is-mysql-caching-the-column-names-of-a-tem...

This is a duplicate of Bug #61434.

Thanks Valeriy Kravchuk  - agree it sounds like the same issue - please deal with this as appropriate within procedure here