MySQL Bugs: #15217: Using a SP cursor on a table created with PREPARE fails with weird error

Bug #15217	Using a SP cursor on a table created with PREPARE fails with weird error
Submitted:	24 Nov 2005 10:43	Modified:	23 Jul 2006 3:58
Reporter:	Anders Karlsson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0.16	OS:	Windows (Windows XP)
Assigned to:	Konstantin Osipov	CPU Architecture:	Any

Description:
Creating a table using a PREPARE in a stored procedure, and then opening a cursor on this, fails under certain circumstances. The testcase shows a case where this does happen, a slight change to this code, for example doing a INSERT INTO ... SELECT in one PREPARE seems to work, whereas doing it in two steps, and in the reproducible case, fails. As the error message seems to indicate that this is a pointer gone astray, this seems serious enough, although in this reproducible case all you get is an error.

How to repeat:
Run the following from, say, MySQL Command prompt. The first call will succeed, assuming there is no "tmp1" table. The second will fail:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1(c1 INT);
INSERT INTO t1 VALUES(1);

DROP PROCEDURE IF EXISTS p1;
delimiter //
CREATE PROCEDURE p1(tab VARCHAR(64))
BEGIN
	DECLARE var1 CHAR(255);

	DROP TEMPORARY TABLE IF EXISTS tmp1;
	SET @stmt = CONCAT('CREATE TEMPORARY TABLE tmp1 LIKE ', tab);
	PREPARE p1 FROM @stmt;
	EXECUTE p1;
	DEALLOCATE PREPARE p1;
	SET @stmt = CONCAT('INSERT INTO tmp1 SELECT * FROM ', tab);
	PREPARE p1 FROM @stmt;
	EXECUTE p1;
	DEALLOCATE  PREPARE p1;

	BEGIN
		DECLARE cur1 CURSOR FOR SELECT * FROM tmp1;

		OPEN cur1;
		FETCH cur1 INTO var1;
		SELECT CONCAT('Data was: /', var1, '/');
		CLOSE cur1;
	END;
END
//
delimiter ;

CALL p1('t1');
CALL p1('t1');

Result from the last two statements in my environment:
+----------------------------------+
| CONCAT('Data was: /', var1, '/') |
+----------------------------------+
| Data was: /1/                    |
+----------------------------------+
1 row in set (0.07 sec)

Query OK, 0 rows affected, 1 warning (0.07 sec)

ERROR 1054 (42S22): Unknown column 'test.t1.:╢♥É:╢♥≡:╢♥i▌╗♥Pâa' in 'field list'

What I meant to say what seems to work was a CREATE TABLE ... SELECT in one go.

Verified, in my case i get 

ERROR 1054 (42S22): Unknown column '��.IGN_COLS�����h40.c1' in 'field list'

on the last statement

Was able to repeat without temporary tables:
drop table if exists t1;
create table t1(c1 int);
insert into t1 values(1);

drop procedure if exists p1;
delimiter //
create procedure p1(tab varchar(64))
begin
  drop table if exists t2;
  set @stmt= concat('create table t2 as select * from ', tab);
  prepare p1 from @stmt;
  execute p1;
  deallocate  prepare p1;
  begin
    declare var1 char(255);
    declare cur1 cursor for select * from t2;
    open cur1;
    fetch cur1 into var1;
    select concat('data was: /', var1, '/');
    close cur1;
  end;
end//
delimiter ;

call p1('t1');
call p1('t1');

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/7738

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/8086

Pushed into 5.0.23 merged up to 5.1.12

Noted in 5.0.23, 5.1.12 changelogs.

Re-executing a stored procedure with a complex stored procedure 
cursor query could lead to a server crash.