Bug #9150 DDL Statements in Stored Procedures Not Parsing for Variable Replacement

• Submitted: 13 Mar 2005 2:27
• Modified: 30 Mar 2005 11:25
• Reporter: [ name withheld ]
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.2
• OS: MacOS (Mac OS X 10.3)
• Assigned to: Per-Erik Martin
• CPU Architecture: Any

[13 Mar 2005 2:27] [ name withheld ]

Description:
It appears that DDL statements are not being parsed for variable replacement.  DML is working fine for variable replacement.  I haven't tried all DDL, just create table and alter table.

'njoy,
Mark

How to repeat:
DROP TABLE IF EXISTS tbl;
CREATE TABLE tbl (
  id int(11) NOT NULL auto_increment,
  name varchar(40) NOT NULL default '',
  ts datetime NOT NULL default '2004-01-01 00:00:00',
  PRIMARY KEY  (id),
  KEY name (name)
);

DROP PROCEDURE IF EXISTS add_tbl;
DELIMITER //
CREATE PROCEDURE add_tbl (IN in_name varchar(40))
BEGIN
  INSERT INTO
    tbl
  VALUES
    (NULL, in_name, now());
  CREATE TABLE
    in_name
    ( id int not null auto_increment primary key,
      name varchar(40) not null default '',
      ts datetime not null default '2004-01-01 00:00'
    );
END//
DELIMITER ;

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| casc           |
| col            |
| profile        |
| tbl            |
+----------------+
4 rows in set (0.04 sec)

mysql> call add_tbl('mark');
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| casc           |
| col            |
| in_name        |
| profile        |
| tbl            |
+----------------+
5 rows in set (0.00 sec)

Variable replacement works fine for the insert . . . 

mysql> select * from tbl;
+----+------+---------------------+
| id | name | ts                  |
+----+------+---------------------+
|  1 | mark | 2005-03-12 21:19:11 |
+----+------+---------------------+
1 row in set (0.00 sec)

Same goes for "alter table"

CREATE TABLE col (
  id int(11) NOT NULL auto_increment,
  name varchar(40) NOT NULL default '',
  tbl varchar(40) NOT NULL default '',
  ts datetime NOT NULL default '2004-01-01 00:00:00',
  PRIMARY KEY  (id),
  KEY name (name),
  KEY tbl (tbl)
);

DROP PROCEDURE IF EXISTS add_col;
DELIMITER //
CREATE PROCEDURE add_col (IN in_name varchar(40), IN in_tbl varchar(40))
BEGIN
  INSERT INTO
    col 
  VALUES
    (NULL, in_name, in_tbl, now());
  ALTER TABLE
    in_tbl
  ADD
    in_name int not null default 0;
END//
DELIMITER ;

mysql> rename table in_name to mark;
Query OK, 0 rows affected (0.00 sec)

mysql> call add_col('testcol', 'mark');
ERROR 1146 (42S02): Table 'test.in_tbl' doesn't exist

mysql> rename table mark to in_tbl;
Query OK, 0 rows affected (0.00 sec)

mysql> call add_col('testcol', 'mark');
Query OK, 0 rows affected (0.01 sec)

mysql> show create table in_tbl;
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                             |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| in_tbl | CREATE TABLE `in_tbl` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(40) NOT NULL default '',
  `ts` datetime NOT NULL default '2004-01-01 00:00:00',
  `in_name` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> select * from col;
+----+---------+------+---------------------+
| id | name    | tbl  | ts                  |
+----+---------+------+---------------------+
|  1 | testcol | mark | 2005-03-12 21:23:08 |
|  2 | testcol | mark | 2005-03-12 21:24:09 |
+----+---------+------+---------------------+
2 rows in set (0.00 sec)

[30 Mar 2005 11:25] Per-Erik Martin

This is not a bug, it functioning as designed and according to the standard.
Stored procedure variables are expression values, it's not doing arbitrary
text substitution.

[30 Mar 2005 14:50] [ name withheld ]

I have a genuine business need and facilities exist in stored procedures in every other DB.  Do you have a workaround that you can suggest?

'njoy,
Mark

[30 Mar 2005 16:28] Per-Erik Martin

What you need is the "EXEC" feature (as it's called in some DBs), which is currently not available.
It is however possible to work around using prepared statements. It's a bit awkard
dure to some limitations in prepared statements (the use of session variables), but
this works:

CREATE PROCEDURE add_tbl (IN in_name varchar(40))
BEGIN
  INSERT INTO
    tbl
  VALUES
    (NULL, in_name, now());

  set @s = concat('CREATE TABLE ', in_name, ' ( id int not null auto_increment primary key, name varchar(40) not null default "", ts datetime not null default "2004-01-01 00:00" )');
  prepare stmt from @s;
  execute stmt;
  deallocate prepare stmt;
END//