Bug #61434 PREPAREd statements and TEMPORARY TABLEs in stored PROCEDUREs
Submitted: 7 Jun 2011 12:25 Modified: 20 Jun 2012 15:47
Reporter: Christopher Jung Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.5.14, 5.5.11 OS:Any (Server 2008 R2 64bit, Mac OS X)
Assigned to: CPU Architecture:Any
Tags: prepare, PROCEDURE, stored, table, temporary

[7 Jun 2011 12:25] Christopher Jung
Description:
SELECT * statements in stored procedures seem to have out-of-date information on table columns of temporary tables that have been created in stored procedures with use of PREPARE statements.

How to repeat:
USE `ccx2`;
DROP procedure IF EXISTS `test`;

DELIMITER $$
USE `ccx2`$$
CREATE DEFINER=`root`@`%` PROCEDURE `test`(name1 varchar(20))
BEGIN
    DROP TEMPORARY TABLE IF EXISTS TEMP_FilterTable;

    SET @q = CONCAT('CREATE TEMPORARY TABLE TEMP_FilterTable(',name1,' TEXT);');
    SET @i = CONCAT('INSERT INTO TEMP_FilterTable (',name1,') VALUE (''123'');');

    PREPARE q FROM @q;
    EXECUTE q;
    PREPARE i FROM @i;
    EXECUTE i;

    SELECT * FROM TEMP_FilterTable;
END$$
DELIMITER ;

CALL test('t1');
CALL test('t2');

# Results in an error on the second CALL:
# 'Error Code: 1054
# Unknown column 'ccx2.TEMP_FilterTable.t1' in 'field list'

Suggested fix:
SELECT * should use up-to-date information of table columns.
[8 Jun 2011 6:35] Valeriy Kravchuk
Thank you for the problem report. Verified with current mysql-5.5 from bzr on Mac OS X:

mysql> delimiter $$
mysql> create PROCEDURE `test`(name1 varchar(20))
    -> BEGIN
    ->     DROP TEMPORARY TABLE IF EXISTS TEMP_FilterTable;
    -> 
    ->     SET @q = CONCAT('CREATE TEMPORARY TABLE TEMP_FilterTable(',name1,' TEXT);');
    ->     SET @i = CONCAT('INSERT INTO TEMP_FilterTable (',name1,') VALUE (''123'');');
    -> 
    ->     PREPARE q FROM @q;
    ->     EXECUTE q;
    ->     PREPARE i FROM @i;
    ->     EXECUTE i;
    ->     deallocate prepare q;
    ->     deallocate prepare i;
    -> 
    ->     SELECT * FROM TEMP_FilterTable;
    -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> CALL test('t1');
+------+
| t1   |
+------+
| 123  |
+------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> CALL test('t2');
ERROR 1054 (42S22): Unknown column 'test.TEMP_FilterTable.t1' in 'field list'
[20 Jun 2012 15:47] Paul Dubois
Noted in 5.6.6 changelog entry.

SELECT * within a stored program could fail for TEMPORARY tables
created within the program using prepared statements.
[11 Jul 2012 4:18] Valeriy Kravchuk
Bug #65869 was marked as a duplicate of this bug.
[9 Oct 2012 16:42] Yuval Cohen
Note: This happenes not only to temporary tables, but also to non-temporary tables
[9 Nov 2012 8:01] MySQL Verification Team
Fixed in 5.6.6 and higher.