MySQL Bugs: #15263: Undeclared variable: <name> for PREPARE of SELECT ... INTO <name>

Bug #15263	Undeclared variable: <name> for PREPARE of SELECT ... INTO <name>
Submitted:	26 Nov 2005 15:37	Modified:	3 Apr 2013 1:49
Reporter:	Jan Kneschke	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0.16	OS:	Linux (Linux/x86)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
I can't PREPARE a SELECT ... INTO ... statement in Stored Procedures as the variables I want to fetch into are not visible inside the prepared statement.

Neither PREPARE statement nor the SELECT ... INTO docs reference this is limitation.

How to repeat:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (id INT);
INSERT INTO t1 VALUES ( 1 );

DELIMITER $$
DROP PROCEDURE IF EXISTS sp$$
CREATE PROCEDURE sp(OUT rid INT)
BEGIN
  SELECT id INTO rid FROM t1;
END$$
DELIMITER ;
CALL sp(@id);
SELECT @id;
# @id
# 1

DELIMITER $$
DROP PROCEDURE IF EXISTS sp$$
CREATE PROCEDURE sp(OUT rid INT)
BEGIN
  PREPARE stmt FROM 'SELECT id INTO @foo FROM t1';
  EXECUTE stmt;

  SET rid = @foo;
END$$
DELIMITER ;
CALL sp(@id);
SELECT @id;
# @id
# 1

DELIMITER $$
DROP PROCEDURE IF EXISTS sp$$
CREATE PROCEDURE sp(OUT rid INT)
BEGIN
  PREPARE stmt FROM 'SELECT id INTO rid FROM t1';
  EXECUTE stmt;
END$$
DELIMITER ;
CALL sp(@id);
# ERROR 1327 (42000) at line 23: Undeclared variable: rid
SELECT @id;

Suggested fix:
Is this a scope issue as the prepared statement can declared in the SP and used after the SP is finished ? If yes, add this limitation to the manual.

Otherwise: make the declared variables visible inside of prepared statements.

Thank you for a problem report. But I think it is not a bug. As clearly stated in the manual (http://dev.mysql.com/doc/refman/5.0/en/sqlps.html):

"Parameter markers can be used only where data values should appear, not for SQL keywords, identifiers, and so forth."

So, we can not do:

PREPARE stmt FROM 'SELECT id INTO ? FROM t1';

We can not use ? instead of identifier (variable name).

And when you use parameter name:

PREPARE stmt FROM 'SELECT id INTO rid FROM t1';

in the string being prepared, then server simply do not know what that rid refers to in statement you are trying to prepare. You may try to prepare it outside SP with the same result:

mysql> prepare stmt from 'select id into p from t1';
ERROR 1327 (42000): Undeclared variable: p

I agree that this should be noted somewhere in the manual explicitely. I'll send a request to our documentation team about that.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Actually this can be solved by putting an @ in front of your variable.

example:
v_query = 'select count(*) into @var_1 from table_1';
set @add_where = concat(v_query, ' where col1 > 1');
prepare stmt from @add_where;
execute stmt;

select @var_1 (your count)

DELIMITER $$
DROP PROCEDURE IF EXISTS sp$$
CREATE PROCEDURE sp(OUT rid INT)
BEGIN
  /* SET @foo=NULL; */
  /* the user-defined variable @foo should be set NULL explicitly because of the fact that 'INTO @foo' subclause will not be executed (@foo will retain to be the last value due to connection scope of user-defined variables) if the result set of select is empty.*/
  PREPARE stmt FROM 'SELECT id INTO @foo FROM t1';
  EXECUTE stmt;

  SET rid = @foo;
END$$
DELIMITER ;
CALL sp(@id);
SELECT @id;
# @id
# 1

Let's just make sure that this workaround is documented, at http://dev.mysql.com/doc/refman/5.5/en/prepare.html, http://dev.mysql.com/doc/refman/5.5/en/create-procedure.html and/or in other places.

http://dev.mysql.com/doc/refman/5.6/en/local-variable-scope.html

"
Local variables are in scope only during stored program execution. References to them are not permitted within prepared statements because those are global to the current session and the variables might have gone out of scope when the statement is executed. For example, SELECT ... INTO local_var cannot be used as a prepared statement.
"

So this issue is not specific to SELECT ... INTO. But it also applies to routine parameters (not just local variables), and it needs to be documented in other places, such as the PREPARE description.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Added/updated documentation of the prohibition on stored program parameters and local variables here:

http://dev.mysql.com/doc/refman/5.6/en/prepare.html
http://dev.mysql.com/doc/refman/5.6/en/create-procedure.html
http://dev.mysql.com/doc/refman/5.6/en/local-variable-scope.html
http://dev.mysql.com/doc/refman/5.6/en/stored-program-restrictions.html