MySQL Bugs: #13833: stored procedure parameter not substituted in CREATE/DROP TABLE

Bug #13833	stored procedure parameter not substituted in CREATE/DROP TABLE
Submitted:	7 Oct 2005 10:37	Modified:	7 Oct 2005 11:20
Reporter:	Frank Maas (Basic Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.0.13rc	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
If a parameter is used to name a table in a CREATE/DROP TABLE statement, its value is not extrapolated, in stead a table with the parameter name is created/dropped.

How to repeat:
delimiter $$
drop procedure if exists `cre_tbl`$$
create procedure `cre_tbl` (in y varchar(10))
begin drop table if exists y; create table y (id int); end $$
delimiter ;

call cre_tbl('a');

/* this results in a 'table "a" does not exist' */
select * from a;

/* this results in an empty resultset */
select * from y;

Suggested fix:
Had the procedure contained something like

...where bla = y

then y would be subsituted with the value of the SP parameter y. I think one (or at least I) would expect this behaviour to be wherever the SP parameter is used.

recategorised (the initial bugreport form only has "MySQL Server" als a category and not the subcategories).

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

You may ask to add some feature to make it possible. But now it is surely impossible in SP.

To clearify: Variables are substituted in expressions, and the table name is not. (This is all according to the SQL-99/2003 standard.)

But it's not impossible, you can concatenate a string and use prepared statements.
(A bit awkward, but it works.)

I do not completely understand the current status of this bug. (a) you write that you see this as a feature request and then close the bug. Why not simply change the severity to S4. Now I have to enter it again (or am I missing something). (b) to the fact that this is not a bug: you state that it is supposed to be not supported - in that case: why is the documentation is lacking that information? Both documentation and article at lease give the impression that you can use parameters wherever you want in (the body of) an SP...?

OK, I stand corrected if this is according to SQL spec. Please disregard my comment part (b). With respect to the workaround: could you elaborate on using concatenate and string?