MySQL Bugs: #40038: variables declared inside store procedures should build prepared statements

Bug #40038	variables declared inside store procedures should build prepared statements
Submitted:	15 Oct 2008 9:56	Modified:	15 Oct 2008 11:10
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S3 (Non-critical)
Version:	any	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html

"A prepared statement is also global to the connection. If you create a prepared statement within a stored routine, it is not deallocated when the stored routine ends."

But still I do not see why only @uservariables and not variables declared inside store procedures can be used in PREPARE statement. The local variable is lost when the Procedure ends of course, but at that time the *prepared statement* has been defined!

How to repeat:
-- works
DELIMITER $$
CREATE  PROCEDURE `test`.`prstmttest1`()
BEGIN
set @mystmt = 'select 1';
prepare stmt from @mystmt;
execute stmt;
END$$
DELIMITER ;

-- also works
DELIMITER $$
CREATE  PROCEDURE `test`.`prstmttest2`()
BEGIN
set @mystmt = 'select ';
set @mystmt = concat(@mystmt,(select 1));
prepare stmt from @mystmt;
execute stmt;
END$$
DELIMITER ;

-- does not work
DELIMITER $$
CREATE  PROCEDURE `test`.`prstmttest3`()
BEGIN
DECLARE mystmt varchar(50);
set mystmt = 'select 1';
prepare stmt from mystmt; -- it fails here
execute stmt;
END$$
DELIMITER ;

-- also this works
DELIMITER $$
CREATE  PROCEDURE `test`.`prstmttest4`()
BEGIN
DECLARE mystmt varchar(50);
set mystmt = 'select 1';
set @mystmt = mystmt;
prepare stmt from @mystmt;
execute stmt;
END$$
DELIMITER ;
-- but why have the @uservariable after PREPARE has finished (using memory)

Suggested fix:
consider 

1) document that variables passed to PREPARE statements must be @uservariables and that variables declared locally inside store procedures won't work with PREPARE statement.

2) (better) allow for variables declared localaly inside store procedures in PREPARE syntax

Thank you for a problem report. Your suggestion #2 sounds like a reasonable feature request for me.

-- works
DELIMITER $$
CREATE  PROCEDURE `test`.`prstmttest5`()
BEGIN
prepare stmt from 'select 1';
execute stmt;
END$$
DELIMITER ;

-- fails
DELIMITER $$
CREATE  PROCEDURE `test`.`prstmttest6`()
BEGIN
DECLARE mystmt varchar(50);
set mystmt = 'select 1';
prepare stmt from concat(mystmt,'');
execute stmt;
END$$
DELIMITER ;

-- also fails ?????
DELIMITER $$
CREATE  PROCEDURE `test`.`prstmttest7`()
BEGIN
set @mystmt = 'select 1';
prepare stmt from concat(@mystmt,'');
execute stmt;
END$$
DELIMITER ;

Are they not all (mystmt, @mystmt, 'select 1', concat(@mystmt,'')) just strings?

It would be very nice to be able to wrap building a complex prepared statement in a SP using locally declared variables only to avoid 'collisions' of @uservariables! (the EXECUTE would then be outside the SP).

... so in summary the request is that *all valid string expressions* should be supported as argument for PREPARE - not only 'constant strings' and @uservariables as of now!

re:

"1) document that variables passed to PREPARE statements must be @uservariables and that
variables declared locally inside store procedures won't work with PREPARE statement."

For the first part of that (what is acceptable to pass to PREPARE) http://dev.mysql.com/doc/refman/5.1/en/prepare.html says:

"PREPARE stmt_name FROM preparable_stmt

preparable_stmt is either a string literal or a user variable that contains the text of the statement."

That rules out local routine variables.

Whether that is reasonable is another question, but the allowable sources of statement text are documented.