Bug #66507 SELECT INTO var in Prepared Statements

• Submitted: 22 Aug 2012 21:48
• Modified: 23 Aug 2012 6:03
• Reporter: Federico Razzoli (OCA)
• Status: Not a Bug
• Category: MySQL Server: Prepared statements
• Severity: S3 (Non-critical)
• OS: Any
• CPU Architecture: Any

[22 Aug 2012 21:48] Federico Razzoli

Description:
I'm not sure if this is a bug or a feature request.

SELECT ... INTO @var_name does not work into a prepared statement: an error says that @var_name is not defined (but it IS defined).

This is important in stored routines, because prepared statements are the only way to compose a dynamic SQL statement, and we cannot read the results if INTO does not work.

How to repeat:
CREATE PROCEDURE xxx(IN db TEXT, IN tab TEXT, IN col TEXT, IN val TEXT, IN msg CHAR(255))
BEGIN
	DECLARE res BOOL DEFAULT FALSE;
	
	-- compose query
	SET @cmd = CONCAT('SELECT EXISTS (SELECT 1 FROM `', db, '`.`', tab, '` ',
			'WHERE `', col, '` IS NULL) INTO `res`;');
	
	-- run query
	PREPARE stmt FROM @cmd;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
END ||

[23 Aug 2012 6:03] Valeriy Kravchuk

You use local variable from procedure in your prepared statement (not user variable!), and in that context it is really not visible. 

Try something like this (get result into a user variable, then assign it to local variable in procedure if you want):

mysql> set @cmd = 'select ? into @res';
Query OK, 0 rows affected (0.00 sec)

mysql> prepare stmt from @cmd;
Query OK, 0 rows affected (0.05 sec)
Statement prepared

mysql> set @val=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select @res;
+------+
| @res |
+------+
| NULL |
+------+
1 row in set (0.02 sec)

mysql> execute stmt using @val;
Query OK, 1 row affected (0.03 sec)

mysql> select @res;
+------+
| @res |
+------+
|    1 |
+------+
1 row in set (0.00 sec)