Bug #19182 CREATE TABLE bar (m INT) SELECT n FROM foo; doesn't work from stored procedure.
Submitted: 18 Apr 2006 23:53 Modified: 4 Jun 2007 18:01
Reporter: Danny Holstein Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.1 OS:Linux (Linux, WinXP)
Assigned to: Konstantin Osipov CPU Architecture:Any
Tags: rt_q1_2007

[18 Apr 2006 23:53] Danny Holstein
Description:
I've tried the following example with MySQL 5.0.19, it works by itself.  It DOESN'T work from a stored procedure, the first column IS NOT created.

mysql> CREATE TABLE bar (m INT) SELECT n FROM foo;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM bar;
+------+---+
| m    | n |
+------+---+
| NULL | 1 |
+------+---+
1 row in set (0.00 sec)

How to repeat:
This is the code that doesn't execute correctly from the procedure, it does work by itself:

SELECT date(min(BegTime)) FROM hits.time_hits INTO @startDT;

DROP PROCEDURE IF EXISTS clickiterate;
delimiter //
CREATE PROCEDURE clickiterate(IN startDT DATETIME)
BEGIN
  label2: LOOP
    SET @week_end=DATE_ADD(startDT, INTERVAL 3 DAY);
    DROP TABLE IF EXISTS temp;
    CREATE TABLE temp (ID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(ID))
      SELECT BegTime FROM hits.time_hits
       WHERE BegTime>DATE_SUB(startDT, INTERVAL .25 DAY) AND
                              BegTime<DATE_ADD(startDT, INTERVAL 3.25 DAY);
    ALTER TABLE temp DROP PRIMARY KEY, ADD PRIMARY KEY(`ID`, `BegTime`), ENGINE = MEMORY;
    label1: LOOP
      SELECT count(*)
             FROM temp WHERE BegTime>DATE_SUB(startDT, INTERVAL 30 MINUTE) AND
                                  BegTime<DATE_ADD(startDT, INTERVAL 30 MINUTE)
                        	INTO @P;
      IF @P IS NULL THEN SET @P=0; END IF;
      UPDATE hits.ordersales SET hits_per_hr=@P where OrderDT=startDT;
      SET startDT=DATE_ADD(startDT, INTERVAL 10 MINUTE);
      IF startDT < @week_end THEN ITERATE label1; END IF;
      LEAVE label1;
    END LOOP label1;
    SET startDT = @week_end;

    IF startDT < now() THEN ITERATE label2; END IF;
    LEAVE label2;
  END LOOP label2;
END
//
delimiter ;

CALL clickiterate(@startDT);
[19 Apr 2006 8:48] Valeriy Kravchuk
Thank you for a problem report. Please, send a self-contained test case, with all the tables referenced in your procedure. Or, even better, a simpler one, showing that CREATE TABLE bar ... SELECT .. FROM foo; does not work as expected (and nothing else).

Please, also try to repeat with a newer version, 5.0.20.
[19 Apr 2006 15:13] Danny Holstein
The following causes an error -- it executes correctly on the first iteration but not with the following iterations, column A in table temp1 isn't there:

DROP TABLE IF EXISTS temp;
CREATE TABLE temp (B INT, C INT);
INSERT INTO temp (B,C) VALUES(2,3),(5,6),(8,9);

DROP TABLE IF EXISTS temp1;
CREATE TABLE temp1 (A INT AUTO_INCREMENT, PRIMARY KEY (A)) 
  SELECT B,C FROM temp;
SELECT * FROM temp1;

DROP PROCEDURE IF EXISTS dum_proc;
delimiter //
CREATE PROCEDURE dum_proc()
BEGIN
  SET @I=1;
  label2: LOOP
    DROP TABLE IF EXISTS temp1;
    CREATE TABLE temp1 (A INT AUTO_INCREMENT, PRIMARY KEY (A)) 
      SELECT B,C FROM temp;
    SELECT * FROM temp1;
    SET @I=@I + 1;
    IF @I < 5 THEN ITERATE label2; END IF;
    LEAVE label2;
  END LOOP label2;
END
//
delimiter ;

CALL dum_proc();
[20 Apr 2006 17:30] Valeriy Kravchuk
Verified just as described in the last comment on 5.0.21-BK (ChangeSet@1.2084.92.1, 2006-04-20 12:06:22+02:00):

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.21

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> DROP TABLE IF EXISTS temp;
Query OK, 0 rows affected, 1 warning (0.01 sec)

Cmysql> CREATE TABLE temp (B INT, C INT);
INSQuery OK, 0 rows affected (0.02 sec)

Emysql> INSERT INTO temp (B,C) VALUES(2,3),(5,6),(8,9);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> DROP TABLE IF EXISTS temp1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE temp1 (A INT AUTO_INCREMENT, PRIMARY KEY (A))
    ->   SELECT B,C FROM temp;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM temp1;
+---+------+------+
| A | B    | C    |
+---+------+------+
| 1 |    2 |    3 |
| 2 |    5 |    6 |
| 3 |    8 |    9 |
+---+------+------+
3 rows in set (0.00 sec)

mysql> delimiter //
mysql> CREATE PROCEDURE dum_proc()
    -> BEGIN
    ->   SET @I=1;
    ->   label2: LOOP
    ->     DROP TABLE IF EXISTS temp1;
    ->     CREATE TABLE temp1 (A INT AUTO_INCREMENT, PRIMARY KEY (A))
    ->       SELECT B,C FROM temp;
    ->     SELECT * FROM temp1;
    ->     SET @I=@I + 1;
    ->     IF @I < 5 THEN ITERATE label2; END IF;
    ->     LEAVE label2;
    ->   END LOOP label2;
    -> END
    -> //
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;
mysql> call dum_proc();
+---+------+------+
| A | B    | C    |
+---+------+------+
| 1 |    2 |    3 |
| 2 |    5 |    6 |
| 3 |    8 |    9 |
+---+------+------+
3 rows in set (0.02 sec)

ERROR 1054 (42S22): Unknown column 'test.temp1.A' in 'field list'
mysql> call dum_proc();
ERROR 1054 (42S22): Unknown column 'test.temp1.A' in 'field list'
mysql> call dum_proc();
ERROR 1054 (42S22): Unknown column 'test.temp1.A' in 'field list'
[14 Jul 2006 10:34] 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/9166
[25 Oct 2006 18:17] 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/14396

ChangeSet@1.2216, 2006-10-25 22:14:40+04:00, kroki@moonlight.intranet +6 -0
  Bug#19182: CREATE TABLE bar (m INT) SELECT n FROM foo; doesn't work
             from stored procedure.
  
  When CREATE .. SELECT was used from a stored routine or prepared
  statement, on re-execution columns specified in SELECT part could be
  overwritten by columns specified in CREATE part, instead of being added
  to the list of columns.
  
  The cause of a bug was that cached LEX::create_list was modified, and
  then together with LEX::key_list was emtied (instead of being restored to
  original state).
  
  The solution is to restore LEX::create_list to its original state, and to
  not reset LEX::key_list, as it is not modified.
[27 Oct 2006 11:22] 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/14466

ChangeSet@1.2554, 2006-10-27 14:00:07+04:00, kroki@moonlight.intranet +6 -0
  Bug#19182: CREATE TABLE bar (m INT) SELECT n FROM foo; doesn't work
             from stored procedure.
  
  When CREATE .. SELECT was used from a stored routine or prepared
  statement, on re-execution columns specified in SELECT part could be
  overwritten by columns specified in CREATE part, instead of being added
  to the list of columns.
  
  The cause of a bug was that cached LEX::create_list was modified, and
  then together with LEX::key_list was emtied (instead of being restored to
  original state).
  
  The solution is to restore LEX::create_list to its original state, and to
  not reset LEX::key_list, as it is not modified.
[27 Oct 2006 14:00] Tomash Brechko
The bug was in 4.1 too, so it is fixed there.

Queued to 4.1-runtime, 5.0-runtime and 5.1-runtime.
[27 Oct 2006 18:40] Tomash Brechko
It appeared that the patch was not correct.  It has been reverted from runtime team trees.  A new fix is needed.
[31 Oct 2006 18:47] 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/14631

ChangeSet@1.2298, 2006-10-31 21:45:59+03:00, kostja@bodhi.local +13 -0
  A fix and test cases for
  Bug#4968 "Stored procedure crash if cursor opened on altered table"
  Bug#19733 "Repeated alter, or repeated create/drop, fails"
  Bug#19182 "CREATE TABLE bar (m INT) SELECT n FROM foo; doesn't work from 
  stored procedure."
  
  Bug#19182 affects 4.1 and this fix will be ported to 4.1 when (if) it's 
  approved.
  
  The problem that caused all of the above bugs is that functions
  mysql_prepare_table, mysql_create_table and mysql_alter_table are not
  re-execution friendly: during their operation they modify contents
  of LEX (members create_info, alter_info, key_list, create_list),
  thus making the LEX unusable for the next execution.
  In particular, these function remove processed columns and keys from
  create_list, key_list and drop_list. Search the code for drop_it.remove()
  and similar to find evidence.
  
  The fix is to supply to these functions a usable copy of each of the
  above structures at every re-execution of an SQL statement. 
  To simplify memory management, LEX::key_list and LEX::create_list
  were added to LEX::alter_info, a fresh copy of which is created for
  every execution.
[1 Nov 2006 12:57] 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/14676

ChangeSet@1.2298, 2006-11-01 15:56:12+03:00, kostja@bodhi.local +13 -0
  A fix and test cases for
  Bug#4968 "Stored procedure crash if cursor opened on altered table"
  Bug#19733 "Repeated alter, or repeated create/drop, fails"
  Bug#19182 "CREATE TABLE bar (m INT) SELECT n FROM foo; doesn't work from 
  stored procedure."
  Bug#6895 "Prepared Statements: ALTER TABLE DROP COLUMN does nothing"
  
  Bug#19182 affects 4.1 and this fix will be ported to 4.1 when (if) it's 
  approved.
  
  The problem that caused all of the above bugs is that functions
  mysql_prepare_table, mysql_create_table and mysql_alter_table are not
  re-execution friendly: during their operation they modify contents
  of LEX (members create_info, alter_info, key_list, create_list),
  thus making the LEX unusable for the next execution.
  In particular, these function remove processed columns and keys from
  create_list, key_list and drop_list. Search the code for drop_it.remove()
  and similar to find evidence.
  
  The fix is to supply to these functions a usable copy of each of the
  above structures at every re-execution of an SQL statement. 
  To simplify memory management, LEX::key_list and LEX::create_list
  were added to LEX::alter_info, a fresh copy of which is created for
  every execution.
[9 Nov 2006 21:34] Konstantin Osipov
Code reviews were sent by email and IRC.
[17 Jan 2007 17:56] Konstantin Osipov
Pushed into 4.1.23 and 5.0.36, NULL-merged into 5.1. 5.1 requires a separate patch.
[19 Jan 2007 3:57] Paul DuBois
Noted in 4.1.23, 5.0.36, 5.1.15 changelogs.
[19 Jan 2007 21:52] Konstantin Osipov
This bug is still present in 5.1
[28 May 2007 11:43] Konstantin Osipov
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/27449

ChangeSet@1.2515, 2007-05-28 15:30:01+04:00, kostja@vajra.(none) +17 -0
  5.1 version of a fix and test cases for bugs:
  Bug#4968 ""Stored procedure crash if cursor opened on altered table"
  Bug#6895 "Prepared Statements: ALTER TABLE DROP COLUMN does nothing"
  Bug#19182 "CREATE TABLE bar (m INT) SELECT n FROM foo; doesn't work from 
  stored procedure."
  Bug#19733 "Repeated alter, or repeated create/drop, fails"
  Bug#22060 "ALTER TABLE x AUTO_INCREMENT=y in SP crashes server"
  Bug#24879 "Prepared Statements: CREATE TABLE (UTF8 KEY) produces a 
  growing key length" (this bug is not fixed in 5.0)
  
  Re-execution of CREATE DATABASE, CREATE TABLE and ALTER TABLE 
  statements in stored routines or as prepared statements caused
  incorrect results (and crashes in versions prior to 5.0.25).
  
  In 5.1 the problem occured only for CREATE DATABASE, CREATE TABLE
  SELECT and CREATE TABLE with INDEX/DATA DIRECTOY options).
    
  The problem of bugs 4968, 19733, 19282 and 6895 was that functions
  mysql_prepare_table, mysql_create_table and mysql_alter_table are not
  re-execution friendly: during their operation they modify contents
  of LEX (members create_info, alter_info, key_list, create_list),
  thus making the LEX unusable for the next execution.
  In particular, these functions removed processed columns and keys from
  create_list, key_list and drop_list. Search the code in sql_table.cc 
  for drop_it.remove() and similar patterns to find evidence.
    
  The fix is to supply to these functions a usable copy of each of the
  above structures at every re-execution of an SQL statement. 
    
  To simplify memory management, LEX::key_list and LEX::create_list
  were added to LEX::alter_info, a fresh copy of which is created for
  every execution.
    
  The problem of crashing bug 22060 stemmed from the fact that the above 
  metnioned functions were not only modifying HA_CREATE_INFO structure 
  in LEX, but also were changing it to point to areas in volatile memory
  of the execution memory root.
     
  The patch solves this problem by creating and using an on-stack
  copy of HA_CREATE_INFO in mysql_execute_command.
  
  Additionally, this patch splits the part of mysql_alter_table
  that analizes and rewrites information from the parser into
  a separate function - mysql_prepare_alter_table, in analogy with
  mysql_prepare_table, which is renamed to mysql_prepare_create_table.
[28 May 2007 11:43] Konstantin Osipov
Queued into 5.1-runtime
[1 Jun 2007 19:22] Bugs System
Pushed into 5.1.20-beta
[4 Jun 2007 18:01] Paul DuBois
Moved 5.1 changelog entry from 5.1.15 to 5.1.20.