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
• Status: Closed
• Category: MySQL Server: Stored Routines
• Severity: S2 (Serious)
• Version: 5.5.14, 5.5.11
• OS: Any (Server 2008 R2 64bit, Mac OS X)
• 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.