Description:
Within a stored procedure, a SELECT statement run on a table created in the proc with a PREPARE STATEMENT fails to return correct results if the columns in the created table change between proc calls. The columns change because they are determined by the proc parameters. The first call to such a proc always works correctly. Subsequent calls that should produce different columns return either (1) results with the wrong columns or (2)this error: ERROR 1054 (42S22): Unknown column '[database].[table_name].[field_name]' in 'field list'. But if the same SELECT statement is run at the command line afterwards, it returns correct results, so the stored proc appears to be creating the table successfully and MySQL can interpret it correctly.
Within the proc, MySQL appears to "remember" the table columns that were created at the first call after the proc was created even though the table is dropped and the PREPARE STATEMENT is deallocated. Outside the proc, there is no such "memory."
The same thing occurs with CREATE TEMPORARY TABLE, which was what I wanted to use. This bug prevents me from using a proc call for a cross-tab report.
WRONG COLUMNS PROBLEM
Let's say that the first call to the proc creates a table with one column, f1, and then the proc runs SELECT * on the table. Since this is the first call, it will successfully return one column. Then if the next call should create a table with columns f1, f2 and f3 and run SELECT * on this, it'll return only f1--the same as the first call, which is an incorrect result. This happens when a subsequent call creates a table where the columns are a super set of the first call. If I select the table at the command line, outside of a proc, I get all 3 columns--the correct result.
ERROR PROBLEM
After dropping and recreating the procedure, the proc will work correctly again. So let's say the the next call after the drop/create attempts to create a table with columns f1, f2 and f3. This works correctly. But now, if I ask the proc to create a table with column f1 only, the SELECT statement in the proc returns an error: ERROR 1054 (42S22): Unknown column '[db name].[table name].f2' in 'field list'. This occurs every time the new table does not contain all of the initial columns. If I run the same SELECT statement on the table at the command line, I don't get an error but can see that the table exists with one column only now, as it should.
How to repeat:
-- Table set-up
CREATE TABLE t1 (f1 varchar(1), f2 varchar(1), f3 varchar(1));
INSERT INTO t1 (f1, f2, f3) VALUES
('a', 'b', 'c'), ('d', 'e', 'f'), ('g', 'h', 'i'), ('j', 'k', 'l');
-- Create proc that will construct CREATE TABLE statement and output the text,
-- create PREPARE STATEMENT from this, execute it and then SELECT from
-- the created table. Parameters determine columns in created table.
DROP PROCEDURE IF EXISTS p_tst;
DELIMITER //
CREATE PROCEDURE p_tst(in_f1 varchar(1), in_f2 varchar(1), in_f3 varchar(1))
BEGIN
DROP TABLE IF EXISTS t2;
SET @s = 'CREATE TABLE t2 AS
SELECT ';
IF in_f1 = 'Y' THEN
SET @s = CONCAT(@s, 'f1, ');
END IF;
IF in_f2 = 'Y' THEN
SET @s = CONCAT(@s, 'f2, ');
END IF;
IF in_f3 = 'Y' THEN
SET @s = CONCAT(@s, 'f3, ');
END IF;
SET @s = SUBSTRING(@s, 1, length(@s) -2);
SET @s = CONCAT(@s, ' FROM t1');
SELECT @s;
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @s = NULL;
SELECT * FROM t2;
END
//
DELIMITER ;
-- Will work since this is initial call to procedure.
CALL p_tst('Y', 'N', 'N');
-- Will return incorrect results. Should return 3 columns. Will return 1 column, same as last call.
-- Note that this same call did not work corretly above.
CALL p_tst('Y', 'Y', 'Y');
-- Shows that table was created correctly even though SELECT doesn't work correctly in proc.
SELECT * FROM t2;
DESC t2;
SELECT column_name FROM information_schema.columns WHERE table_name = 't2';
-- Dropping procedure seems to "reset" proc
DROP PROCEDURE IF EXISTS p_tst;
-- Recreate the same procedure
DELIMITER //
CREATE PROCEDURE p_tst(in_f1 varchar(1), in_f2 varchar(1), in_f3 varchar(1))
BEGIN
DROP TABLE IF EXISTS t2;
SET @s = 'CREATE TABLE t2 AS
SELECT ';
IF in_f1 = 'Y' THEN
SET @s = CONCAT(@s, 'f1, ');
END IF;
IF in_f2 = 'Y' THEN
SET @s = CONCAT(@s, 'f2, ');
END IF;
IF in_f3 = 'Y' THEN
SET @s = CONCAT(@s, 'f3, ');
END IF;
SET @s = SUBSTRING(@s, 1, length(@s) -2);
SET @s = CONCAT(@s, ' FROM t1');
SELECT @s;
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @s = NULL;
SELECT * FROM t2;
END
//
DELIMITER ;
-- Will work since this is initial call to procedure after dropped and recreated.
CALL p_tst('Y', 'Y', 'Y');
-- Will error out since columns in initial call are not included.
CALL p_tst('Y', 'N', 'N');
-- Shows that table was created correctly even though can't select from it in proc.
SELECT * FROM t2;
DESC t2;
SELECT column_name FROM information_schema.columns WHERE table_name = 't2';
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP PROCEDURE p_tst;
Suggested fix:
Fix so that multiple calls to a stored procedure that selects from a table created with a PREPARE STATEMENT, where the created table has variable columns, will return correct results. If unable to do this in short term, could you please document this limitation in "Restrictions on Stored Routines" at
http://dev.mysql.com/doc/refman/5.1/en/stored-program-restrictions.html
Description: Within a stored procedure, a SELECT statement run on a table created in the proc with a PREPARE STATEMENT fails to return correct results if the columns in the created table change between proc calls. The columns change because they are determined by the proc parameters. The first call to such a proc always works correctly. Subsequent calls that should produce different columns return either (1) results with the wrong columns or (2)this error: ERROR 1054 (42S22): Unknown column '[database].[table_name].[field_name]' in 'field list'. But if the same SELECT statement is run at the command line afterwards, it returns correct results, so the stored proc appears to be creating the table successfully and MySQL can interpret it correctly. Within the proc, MySQL appears to "remember" the table columns that were created at the first call after the proc was created even though the table is dropped and the PREPARE STATEMENT is deallocated. Outside the proc, there is no such "memory." The same thing occurs with CREATE TEMPORARY TABLE, which was what I wanted to use. This bug prevents me from using a proc call for a cross-tab report. WRONG COLUMNS PROBLEM Let's say that the first call to the proc creates a table with one column, f1, and then the proc runs SELECT * on the table. Since this is the first call, it will successfully return one column. Then if the next call should create a table with columns f1, f2 and f3 and run SELECT * on this, it'll return only f1--the same as the first call, which is an incorrect result. This happens when a subsequent call creates a table where the columns are a super set of the first call. If I select the table at the command line, outside of a proc, I get all 3 columns--the correct result. ERROR PROBLEM After dropping and recreating the procedure, the proc will work correctly again. So let's say the the next call after the drop/create attempts to create a table with columns f1, f2 and f3. This works correctly. But now, if I ask the proc to create a table with column f1 only, the SELECT statement in the proc returns an error: ERROR 1054 (42S22): Unknown column '[db name].[table name].f2' in 'field list'. This occurs every time the new table does not contain all of the initial columns. If I run the same SELECT statement on the table at the command line, I don't get an error but can see that the table exists with one column only now, as it should. How to repeat: -- Table set-up CREATE TABLE t1 (f1 varchar(1), f2 varchar(1), f3 varchar(1)); INSERT INTO t1 (f1, f2, f3) VALUES ('a', 'b', 'c'), ('d', 'e', 'f'), ('g', 'h', 'i'), ('j', 'k', 'l'); -- Create proc that will construct CREATE TABLE statement and output the text, -- create PREPARE STATEMENT from this, execute it and then SELECT from -- the created table. Parameters determine columns in created table. DROP PROCEDURE IF EXISTS p_tst; DELIMITER // CREATE PROCEDURE p_tst(in_f1 varchar(1), in_f2 varchar(1), in_f3 varchar(1)) BEGIN DROP TABLE IF EXISTS t2; SET @s = 'CREATE TABLE t2 AS SELECT '; IF in_f1 = 'Y' THEN SET @s = CONCAT(@s, 'f1, '); END IF; IF in_f2 = 'Y' THEN SET @s = CONCAT(@s, 'f2, '); END IF; IF in_f3 = 'Y' THEN SET @s = CONCAT(@s, 'f3, '); END IF; SET @s = SUBSTRING(@s, 1, length(@s) -2); SET @s = CONCAT(@s, ' FROM t1'); SELECT @s; PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @s = NULL; SELECT * FROM t2; END // DELIMITER ; -- Will work since this is initial call to procedure. CALL p_tst('Y', 'N', 'N'); -- Will return incorrect results. Should return 3 columns. Will return 1 column, same as last call. -- Note that this same call did not work corretly above. CALL p_tst('Y', 'Y', 'Y'); -- Shows that table was created correctly even though SELECT doesn't work correctly in proc. SELECT * FROM t2; DESC t2; SELECT column_name FROM information_schema.columns WHERE table_name = 't2'; -- Dropping procedure seems to "reset" proc DROP PROCEDURE IF EXISTS p_tst; -- Recreate the same procedure DELIMITER // CREATE PROCEDURE p_tst(in_f1 varchar(1), in_f2 varchar(1), in_f3 varchar(1)) BEGIN DROP TABLE IF EXISTS t2; SET @s = 'CREATE TABLE t2 AS SELECT '; IF in_f1 = 'Y' THEN SET @s = CONCAT(@s, 'f1, '); END IF; IF in_f2 = 'Y' THEN SET @s = CONCAT(@s, 'f2, '); END IF; IF in_f3 = 'Y' THEN SET @s = CONCAT(@s, 'f3, '); END IF; SET @s = SUBSTRING(@s, 1, length(@s) -2); SET @s = CONCAT(@s, ' FROM t1'); SELECT @s; PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @s = NULL; SELECT * FROM t2; END // DELIMITER ; -- Will work since this is initial call to procedure after dropped and recreated. CALL p_tst('Y', 'Y', 'Y'); -- Will error out since columns in initial call are not included. CALL p_tst('Y', 'N', 'N'); -- Shows that table was created correctly even though can't select from it in proc. SELECT * FROM t2; DESC t2; SELECT column_name FROM information_schema.columns WHERE table_name = 't2'; DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; DROP PROCEDURE p_tst; Suggested fix: Fix so that multiple calls to a stored procedure that selects from a table created with a PREPARE STATEMENT, where the created table has variable columns, will return correct results. If unable to do this in short term, could you please document this limitation in "Restrictions on Stored Routines" at http://dev.mysql.com/doc/refman/5.1/en/stored-program-restrictions.html