MySQL Bugs: #2261: Can't use @user_variable as FETCH target in stored procedure

Bug #2261	Can't use @user_variable as FETCH target in stored procedure
Submitted:	2 Jan 2004 14:37	Modified:	23 Sep 2009 16:30
Reporter:	Peter Gulutzan	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0.0	OS:	Linux (SuSE 8.2)
Assigned to:		CPU Architecture:	Any

Description:
I can say "SELECT ... into @user_variable ..." and I can say "FETCH into local_variable" 
but I can't say "FETCH ... into @user_variable". 
 

How to repeat:
delimiter | 
 
mysql> create procedure p23 () begin declare v int; declare c1 cursor for select s1 from 
t11; open c1; fetch c1 into v; end| 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> create procedure p24 () begin declare v int; declare c1 cursor for select s1 from 
t11; open c1; fetch c1 into @x2; end| 
ERROR 1064 (42000): You have an error in your SQL syntax.  Check the manual that 
corresponds to your MySQL server version for the right syntax to use near '@x2; end' at 
line 1

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

This is a deliberate limitation. Since the current cursor implementation
in stored procedures is a "quick-and-dirty" temporary solution until we
have the real cursors, it was deemed not be worth the time to do fetch
into global variables.

I am changing the status back to "Verified".

It appears that there is no longer a justification (or a justifier)
for a "Won't fix" status. No improvement of cursors has solved the
matter to go away in the five years since the bug was reported.
And, unless I have missed something hidden in the manual,
this never became a documented limitation. And the earlier comment
about "global" variables hints that there may have been misunderstanding,
since this report specifically is about variables of the form
'@user_variable', which are legal in an analogous context, for example
"SELECT 5 INTO @user_variable;".

Hi Peter, 

I guess that for consistency's sake, you're right, it should be possible to assign to user-defined variables. But frankly, I think it's bad practice to use it if it were possible. IMO the proper way of achieving this effect would be to do something like:

CREATE PROCEDURE p(OUT p_out INT)
BEGIN
     ...
     FETCH c INTO p_out;
     ...
END;

CALL p(@my_udv);

Excerpt from Winston Churchill's reminiscence of a Latin lesson
http://www.amazon.co.uk/Early-Life-Sir-Winston-Churchill/dp/product-description/0907871623
"
'Mensa, O table, is the vocative case,' he replied.

'But why O table?' I persisted in genuine curiosity.

'O table, - you would use that in addressing a table, in invoking a table.' And then
seeing he was not carrying me with him, 'You would use it in speaking to a table.'

'But I never do,' I blurted out in honest amazement.

'If you are impertinent, you will be punished, and punished, let me tell you, very
severely,' was his conclusive rejoinder.
"

It is the same thing here. Mr Bouman, like Mr Churchill,
observes that "FETCH ... into @user_variable" is something
he would never do. But the teacher's point is that syntax
rules must apply equally and orthogonally for all situations,
including the ones that you do not care to employ.

I think we all agree that using ORDER BY in a cursor query makes sense. User variables are accessible from any query and procedure that will run within the current session. Storing last read values into a user variable makes them more easily accessible.

It's 2020.  Soon to be 2021.  WHY IS THIS STILL A BUG?????