MySQL Bugs: #55678: ALTER TABLE ADD COLUMN in Do While Loop Returns Error

Bug #55678	ALTER TABLE ADD COLUMN in Do While Loop Returns Error
Submitted:	2 Aug 2010 9:43	Modified:	20 Jun 2012 16:04
Reporter:	jamil Ahmad	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	ALTER TABLE, Do While Loop, stored procedure

Description:
I created a stored procedure as,
First i drop the table if exists then creating table by getting data from another table and i have loop to add the columns by given MonthDays in that table.

How to repeat:
mysql> CREATE PROCEDURE `sprrr`(MonthDays INT)
BEGIN

DECLARE Counter INT DEFAULT 1;
DECLARE ColumnNmame VARCHAR(6) DEFAULT '';

DROP TABLE IF EXISTS TStat;
CREATE TABLE TStat AS SELECT * from tbl1;

WHILE Counter <= MonthDays DO  
	
	SET ColumnNmame=CONCAT('D',Counter);
	SET Counter = Counter + 1;  
	SET @sqlStr=CONCAT('ALTER TABLE TStat ADD COLUMN '"",ColumnNmame,""' INT DEFAULT 0;'); 
  
	PREPARE stmt FROM @sqlStr;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;

END WHILE;

SELECT * FROM TStat;

END;

mysql> call sprrr(30);// Query is OK and reterieved the rows with columns D1 to D30. But if we call it again as

mysql> call sprrr(5);// It gives error that Unknown Column 'TStat.D6' in field list. But when we checked the table TStat its perfect and Altered the columns with the perfect range D1 to D5

Verified as described:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.48-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table tbl1(id int, c1 int);
Query OK, 0 rows affected (0.42 sec)

mysql> delimiter //
mysql> CREATE PROCEDURE `sprrr`(MonthDays INT)
    -> BEGIN
    ->
    -> DECLARE Counter INT DEFAULT 1;
    -> DECLARE ColumnNmame VARCHAR(6) DEFAULT '';
    ->
    -> DROP TABLE IF EXISTS TStat;
    -> CREATE TABLE TStat AS SELECT * from tbl1;
    ->
    -> WHILE Counter <= MonthDays DO
    ->
    ->  SET ColumnNmame=CONCAT('D',Counter);
    ->  SET Counter = Counter + 1;
    ->  SET @sqlStr=CONCAT('ALTER TABLE TStat ADD COLUMN '"",ColumnNmame,""' INT
 DEFAULT 0;');
    ->
    ->  PREPARE stmt FROM @sqlStr;
    ->  EXECUTE stmt;
    ->  DEALLOCATE PREPARE stmt;
    ->
    -> END WHILE;
    ->
    -> SELECT * FROM TStat;
    ->
    -> END;
    -> //
Query OK, 0 rows affected (0.33 sec)

mysql> call sprrr(30)//
Empty set (6.25 sec)

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

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1051
Message: Unknown table 'tstat'
1 row in set (0.00 sec)

mysql> show create table tstat\G
*************************** 1. row ***************************
       Table: tstat
Create Table: CREATE TABLE `tstat` (
  `id` int(11) DEFAULT NULL,
  `c1` int(11) DEFAULT NULL,
  `D1` int(11) DEFAULT '0',
  `D2` int(11) DEFAULT '0',
  `D3` int(11) DEFAULT '0',
  `D4` int(11) DEFAULT '0',
  `D5` int(11) DEFAULT '0',
  `D6` int(11) DEFAULT '0',
  `D7` int(11) DEFAULT '0',
  `D8` int(11) DEFAULT '0',
  `D9` int(11) DEFAULT '0',
  `D10` int(11) DEFAULT '0',
  `D11` int(11) DEFAULT '0',
  `D12` int(11) DEFAULT '0',
  `D13` int(11) DEFAULT '0',
  `D14` int(11) DEFAULT '0',
  `D15` int(11) DEFAULT '0',
  `D16` int(11) DEFAULT '0',
  `D17` int(11) DEFAULT '0',
  `D18` int(11) DEFAULT '0',
  `D19` int(11) DEFAULT '0',
  `D20` int(11) DEFAULT '0',
  `D21` int(11) DEFAULT '0',
  `D22` int(11) DEFAULT '0',
  `D23` int(11) DEFAULT '0',
  `D24` int(11) DEFAULT '0',
  `D25` int(11) DEFAULT '0',
  `D26` int(11) DEFAULT '0',
  `D27` int(11) DEFAULT '0',
  `D28` int(11) DEFAULT '0',
  `D29` int(11) DEFAULT '0',
  `D30` int(11) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> call sprrr(5)//
ERROR 1054 (42S22): Unknown column 'test.TStat.D6' in 'field list'
mysql> show create table tstat\G
*************************** 1. row ***************************
       Table: tstat
Create Table: CREATE TABLE `tstat` (
  `id` int(11) DEFAULT NULL,
  `c1` int(11) DEFAULT NULL,
  `D1` int(11) DEFAULT '0',
  `D2` int(11) DEFAULT '0',
  `D3` int(11) DEFAULT '0',
  `D4` int(11) DEFAULT '0',
  `D5` int(11) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> call sprrr(10)//
ERROR 1054 (42S22): Unknown column 'test.TStat.D11' in 'field list'
mysql> show create table tstat\G
*************************** 1. row ***************************
       Table: tstat
Create Table: CREATE TABLE `tstat` (
  `id` int(11) DEFAULT NULL,
  `c1` int(11) DEFAULT NULL,
  `D1` int(11) DEFAULT '0',
  `D2` int(11) DEFAULT '0',
  `D3` int(11) DEFAULT '0',
  `D4` int(11) DEFAULT '0',
  `D5` int(11) DEFAULT '0',
  `D6` int(11) DEFAULT '0',
  `D7` int(11) DEFAULT '0',
  `D8` int(11) DEFAULT '0',
  `D9` int(11) DEFAULT '0',
  `D10` int(11) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> call sprrr(32)//
Empty set (6.63 sec)

Query OK, 0 rows affected (6.63 sec)

Is there any update regarding this bug?

Noted in 5.6.6 changelog.

Adding columns to a table being used within a stored program loop
could cause "Unknown column" errors.