MySQL Bugs: #13572: PREPARE statement breaks on locally declared variables

Bug #13572	PREPARE statement breaks on locally declared variables
Submitted:	28 Sep 2005 15:33	Modified:	6 Feb 2006 9:06
Reporter:	Beat Vontobel (Silver Quality Contributor) (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S4 (Feature request)
Version:	5.0.13	OS:	Any (all)
Assigned to:		CPU Architecture:	Any

Description:
"PREPARE stmt_id FROM statement" doesn't work with locally declared variables in stored procedures.

How to repeat:
Try to create the following stored procedure:

delimiter //
CREATE PROCEDURE prprd_stmt()
BEGIN
    DECLARE query_string VARCHAR(32) DEFAULT 'SELECT 1';
    PREPARE stmt_id FROM query_string;
    EXECUTE stmt_id;
    DEALLOCATE PREPARE stmt_id;
END
//
delimiter ;

It yields "ERROR 1064 (42000): You have an error in your SQL syntax; check..."

Suggested fix:
As a workaround use
    SET @query_string = query_string;
    PREPARE stmt_id FROM @query_string;
instead of just
    PREPARE stmt_id FROM query_string;

verified, not shure whether this is supposed to work though

It's functioning as designed, i.e. PREPARE et al is explicitly designed to work with user variables and constant strings only. (It was introduced in 4.1 where no stored procedures exists.)

I think it should be regarded as a feature request. It's a natural extension in 5.0 to allow local variables in stored procedures, and you can avoid the awkward use of "global" variables. (Actually, I think arbitrary expressions, that evaluate to a string, should be allowed.)

Use of stored procedure variables in SQL syntax for prepared statement will break 
statement-level stored procedure replication.
Regardin use of expressions for prepared statements arguments, there is WL#2793:

--citation
With addition of support for SQL prepared statements to Stored Procedurs, and
use of this server feature for the purposes of Dynamic SQL a convenient syntax for 
creating and executing statements is considered necessary:

1. An arbitrary text that uses intermixedly user variables and string literals
should be allowed for statement text, e.g.

PREPARE stmt_name FROM SELECT * FROM @table_name WHERE a=? and b=@b;

In this syntax all user variables are evaluated at PREPARE and substituted with
their textual contents.

2. In addition to variable names, we should allow string, integer and floating
point literals for statement data, e.g. the following syntax should be supported:

PREPARE stmt_name FROM "SELECT * FROM t1 WHERE a=? AND b=? AND c=?"
EXECUTE stmt_name USING 1, @a, "string constant";
--end citation

> 2. In addition to variable names, we should allow string, integer and floating
> point literals for statement data, e.g. the following syntax should be
> supported:
>
> PREPARE stmt_name FROM "SELECT * FROM t1 WHERE a=? AND b=? AND c=?"
> EXECUTE stmt_name USING 1, @a, "string constant";

This specification should be expanded to explicitly allow local stored routine variables/parameters at least in the USING part of an EXECUTE statement.