Bug #43792 Unable to select * from temporary table in stored procedure

• Submitted: 22 Mar 2009 22:48
• Modified: 23 Mar 2009 6:54
• Reporter: David Frylinck
• Status: Duplicate
• Category: MySQL Server: Stored Routines
• Severity: S3 (Non-critical)
• Version: 5.0.77, 5.1.32
• OS: Windows (Vista)
• CPU Architecture: Any

[22 Mar 2009 22:48] David Frylinck

Description:
When changing the column definition of a temporary table and running select * on it the server attempts to run the same query that was generate for the original definition of the temporary table.

It only seems to happen when the temporary table is created in a stored procedure as the following code works:

DROP TEMPORARY TABLE IF EXISTS `t1`;
create TEMPORARY TABLE t1 (c1 int);
INSERT INTO t1 (c1) VALUES (123);
SELECT * FROM t1;
DROP TEMPORARY TABLE IF EXISTS `t1`;
create TEMPORARY TABLE t1 (c2 int);
INSERT INTO t1 (c2) VALUES (123);
SELECT * FROM t1;

How to repeat:
DELIMITER $$
DROP PROCEDURE IF EXISTS `tmpTest` $$
CREATE PROCEDURE `tmpTest`(inCol varchar(20))
BEGIN
  DROP TEMPORARY TABLE IF EXISTS `t1`;
  set @vSqlText = CONCAT('create TEMPORARY TABLE t1 (', inCol, ' int)');
  prepare stmt from  @vSqlText;
  execute stmt;

  set @vSqlText = CONCAT('INSERT INTO t1 (`', inCol, '`)', ' VALUES (123)');
  prepare stmt from  @vSqlText;
  execute stmt;

  SELECT * FROM t1;
END $$
DELIMITER ;

call tmptest('c1');
call tmpTest('c2');

Suggested fix:
Don't cache queries on temporary tables.
Clear cache for table when dropped.

[23 Mar 2009 6:54] Sveta Smirnova

Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

Duplicate of bug #32868