MySQL Bugs: #23445: DROP USER in PROCEDURE uses variable name, not value

Bug #23445	DROP USER in PROCEDURE uses variable name, not value
Submitted:	18 Oct 2006 21:43	Modified:	26 Oct 2006 17:54
Reporter:	Peter Andrews	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0.26 standard and max	OS:	Linux (Linux)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
calling DROP USER inside a stored procedure with the user/host string stored in an input parameter (or local variable) uses the name of the variable rather than its value

How to repeat:
/* Procedure structure for procedure `drop_user_without_error` */
DROP PROCEDURE IF EXISTS `drop_user_without_error`;
DELIMITER $$

CREATE PROCEDURE `drop_user_test`(
IN user_and_host VARCHAR(77)
)
BEGIN
	DROP USER user_and_host;
END$$
DELIMITER ;

CALL drop_user_test('user@host');

The output is:

(0 row(s)affected)
(0 ms taken)

(0 row(s)affected)
(0 ms taken)

Error Code : 1396
Operation DROP USER failed for 'user_and_host'@''
(4 ms taken)

Thank you for a problem report. Verified just as described with 5.0.27-BK on Linux. I am almost sure that neither user varaibles nor SP paramenter or local variable can be used in CREATE USER, but I do not find where is it explicitely documented. So, I think, it is a reasonable documentation request.

I think this is worse than just a need for documentation and should be a classified as a bug under stored procs/functions.

It should not depend on what the particular use of variable name is inside a stored proc as to whether it is resolved as a variable or not. It seems to me that the implicit contract of a stored proc is that any reference to a variable name will be resolved to its value before being used. 

If this is kept as a documentation error, then the docs for stored procs and functions should also be changed to say 'variables names are always resolved except in these several strange cases...' (which highlights how bad an idea this is).

On further reflection, I think my previous comment was wrong. There are other cases where a variable name is not resolved and we don't expect it to be:

create procedure select_from_table(IN TableNameIn CHAR(20))
begin
   select * from TableNameIn;
end;

I guess the thing to do here is to use dynamic sql via a prepared statement.

Given the previous comment, no action taken.