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:Microsoft Windows (Windows XP)
Assigned to: Konstantin Osipov CPU Architecture:Any

[24 Nov 2005 10:43] Anders Karlsson
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'
[24 Nov 2005 10:52] Anders Karlsson
What I meant to say what seems to work was a CREATE TABLE ... SELECT in one go.
[24 Nov 2005 11:53] Hartmut Holzgraefe
Verified, in my case i get 

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

on the last statement
[7 Apr 2006 11:13] Konstantin Osipov
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');
[16 Jun 2006 1:58] Bugs System
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
[22 Jun 2006 15:39] Bugs System
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
[27 Jun 2006 14:59] Konstantin Osipov
Pushed into 5.0.23 merged up to 5.1.12
[23 Jul 2006 3:58] Paul Dubois
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.