MySQL Bugs: #61711: CURSOR does not accept variables in LIMIT clause

Bug #61711	CURSOR does not accept variables in LIMIT clause
Submitted:	30 Jun 2011 16:45	Modified:	24 Dec 2012 9:36
Reporter:	Matthew Ralston	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.5.13, 5.5.15	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Continuing on from Bug #11918 "SP does not accept variables in LIMIT clause"...

This could do with working for cursors as well.

I have a situation where I need to select an arbitrary number of records from a pool table and add them to a queue table. The number of records which need to be transferred is calculated based on the number of records which are already in the queue table. I would like to cursor through the records being transferred (rather than using INSERT INTO SELECT FROM) because I'll be performing other actions for each record which is transferred.

How to repeat:
CREATE DEFINER=`matt`@`%` PROCEDURE `exampleLimit`()
proc:BEGIN

DECLARE iEof INT DEFAULT 0;
DECLARE iLimit INT;

DECLARE iExampleId INT;
DECLARE sName VARCHAR(255);

DECLARE cExamples CURSOR FOR
	SELECT exampleId, `name`
	FROM example
	LIMIT 0, iLimit;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET iEof=1;

-- TODO: Programatically determine the required limit
SET iLimit=10;

OPEN cExamples;
exampleLoop:LOOP
	SET iEof=0;
	FETCH cExamples INTO iExampleId, sName;
	IF iEof THEN
		LEAVE exampleLoop;
	END IF;

	-- TODO: Something useful...
	SELECT iExampleId, sName;

END LOOP exampleLoop;

END proc

What is the problem here? You can declare cursor with local variable in LIMIT.

You can declare it as such, but when you attempt to run it you get:

ERROR 2013 (HY000): Lost connection to MySQL server during query

Indeed, you are right:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.15-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> delimiter //
mysql> create procedure p1() begin declare var int; declare username varchar(50); declare cur cursor for select user from mysql.user limit 0, var; set var = 1; open cur; fetch cur into username; select username; end;//
Query OK, 0 rows affected (0.00 sec)

mysql> call p1()//
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> 110701 17:34:47 mysqld_safe mysqld restarted

mysql> exit
Bye
macbook-pro:5.5 openxs$ tail -80 data/macbook-pro.err 
...
Version: '5.5.15-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
Assertion failed: (fixed), function val_int, file /Users/openxs/bzr2/mysql-5.5-work/sql/item.cc, line 1123.
110701 17:34:47 - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=8388608
read_buffer_size=131072
max_used_connections=1
max_threads=151
thread_count=1
connection_count=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 337883 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x0 thread_stack 0x30000
0   mysqld                              0x00372581 my_print_stacktrace + 44
1   mysqld                              0x00122dca handle_segfault + 884
2   libSystem.B.dylib                   0x940472bb _sigtramp + 43
3   ???                                 0xffffffff 0x0 + 4294967295
4   mysqld                              0x00128c8b _Z11mysqld_mainiPPc + 2241
5   mysqld                              0x00003672 main + 24
6   mysqld                              0x0000362e start + 54
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
110701 17:34:47 mysqld_safe mysqld restarted
110701 17:34:47 [Warning] Setting lower_case_table_names=2 because file system for /Users/openxs/dbs/5.5/data/ is case insensitive
110701 17:34:47 [Warning] One can only use the --user switch if running as root

110701 17:34:47 [Note] Plugin 'FEDERATED' is disabled.
110701 17:34:47 InnoDB: !!!!!!!! UNIV_DEBUG switched on !!!!!!!!!
110701 17:34:47 InnoDB: The InnoDB memory heap is disabled
110701 17:34:47 InnoDB: Mutexes and rw_locks use GCC atomic builtins
110701 17:34:47 InnoDB: Compressed tables use zlib 1.2.3
110701 17:34:47 InnoDB: Initializing buffer pool, size = 128.0M
110701 17:34:47 InnoDB: Completed initialization of buffer pool
110701 17:34:47 InnoDB: highest supported file format is Barracuda.
110701 17:34:48  InnoDB: Waiting for the background threads to start
110701 17:34:49 InnoDB: 1.1.8 started; log sequence number 7199541
110701 17:34:49 [Note] Event Scheduler: Loaded 0 events
110701 17:34:49 [Note] /Users/openxs/dbs/5.5/bin/mysqld: ready for connections.
Version: '5.5.15-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
macbook-pro:5.5 openxs$

Incidentally, it appears that variables are not allowed anywhere inside a CURSOR definition (not just in the LIMIT clause).

It would be very helpful if variables could be used in CURSORs. So long as all of the relevant variables are initialised before the cursor is OPENed then I don't see that (in theory at least) it should be a problem. I assume that the functionality just isn't coded into MySQL at the moment.

I'm currently using a very nasty work around. I create a temporary table, then I create a prepared statement of the format

INSERT INTO tempTable (field1, field2)
SELECT field1, field2
FROM sourceTable
WHERE field3=?
LIMIT 0, ?;

That prepared statement is executed USING the relevant variables, then I have a cursor which loops through records from the temporary table.

It works, but it's very nasty!

Fixed in 5.5.18 as a duplicate of an internally filed bug.