MySQL Bugs: #19858: nested cursor - insert statement fails to compile in stored procedure

Bug #19858	nested cursor - insert statement fails to compile in stored procedure
Submitted:	16 May 2006 18:09	Modified:	9 Sep 2008 6:25
Reporter:	Chris Huber (Candidate Quality Contributor)	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S2 (Serious)
Version:	14.12 using readline 5.0	OS:	Linux (Linux, Mac OS 10.4.4)
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
I have a stored procedure with a nested cursor. First, I am creating a temporary table from a comma-delimited string of FK ids. 

First cursor: loops through the table of ids. 
Second cursor: selects values that match the FK from another table (can be 0, 1, or many) and inserts them into a second temporary table for processing. 

Inside the second cursor, I wrote an INSERT statement to put the retrieved values into a second temporary table. However, I can't get MySQL to compile it - I get the error: 

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 'INTO second_temp_table (value) VALUES (the_value); 

I know the syntax is correct - I cut and pasted the INSERT statement with an explicit value above the cursors and it compiled and called correctly.

How to repeat:
Here is an example of what I am talking about. First create your utility procedure to handle a comma-delimited list: 

DELIMITER // 
DROP PROCEDURE IF EXISTS list_to_array// 
CREATE PROCEDURE list_to_array (IN input TEXT, IN delimiter VARCHAR(10)) 
SQL SECURITY INVOKER 
BEGIN 
DECLARE cur_position INT DEFAULT 1; 
DECLARE remainder TEXT; 
DECLARE cur_string VARCHAR(1000); 
DECLARE delimiter_length TINYINT UNSIGNED; 
SET remainder = input; 
SET delimiter_length = CHAR_LENGTH(delimiter); 
WHILE CHAR_LENGTH(remainder) > 0 AND cur_position > 0 DO 
SET cur_position = INSTR(remainder, delimiter); 
IF cur_position = 0 THEN 
SET cur_string = remainder; 
ELSE 
SET cur_string = LEFT(remainder, cur_position - 1); 
END IF; 
IF TRIM(cur_string) != '' THEN 
INSERT INTO list_to_array VALUES (cur_string); 
END IF; 
SET remainder = SUBSTRING(remainder, cur_position + delimiter_length); 
END WHILE; 
END 
// 

Then try pasting this into the command-line: 

DROP PROCEDURE IF EXISTS nest_test// 
CREATE PROCEDURE nest_test (IN comma_del_list VARCHAR(500)) 
BEGIN 
DECLARE done INT DEFAULT 0; 
DECLARE test_key, count INT; 
DECLARE result_value DOUBLE; 

DECLARE c1 CURSOR FOR SELECT id FROM list_to_array; 
DECLARE c2 CURSOR FOR SELECT value FROM result WHERE test_app_id = test_key; 

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; 

DROP TEMPORARY TABLE IF EXISTS list_to_array; 
CREATE TEMPORARY TABLE list_to_array(id INT); 

DROP TEMPORARY TABLE IF EXISTS table_two; 
CREATE TEMPORARY TABLE table_two (value_one DOUBLE); 

CALL list_to_array(comma_del_list, ','); 

OPEN c1; 
REPEAT 
FETCH c1 INTO test_key; 
IF NOT done THEN 

OPEN c2; 
REPEAT FETCH c2 INTO result_value; 
IF NOT done THEN 
INSERT INTO table_two (value_one) VALUES ('1'); 
END IF; 
UNTIL done END REPEAT; 
close c2; 
SET done = 0; 

END IF; 
UNTIL done END REPEAT; 
CLOSE c1; 

SELECT * FROM table_two; 
END 
// 

Why is the compiler throwing an exception? I'm stumped, any help with this would be greatly appreciated. If you want to verify that my first function works, just paste: 

SELECT * FROM list_to_array; 
END 
// 

after the CALL statement, compile, and pass in a comma-delimited list as the parameter. Example: 

CALL nest_test('4,5,6,7')//

Suggested fix:
This is a compiler bug from what I can see.

Thank you for a problem report. Please, try to repeat with a newer version of MySQL server, 5.0.21, and inform about the results.

I just ran into a similar problem. It appears that INSERT also fails when put inside an IF-ELSE block. Here is some sample code. (You will need to create a table in the DB that the stored procedure is in with an ID int field and any number of rows >= 1.

This compiles:

DROP PROCEDURE IF EXISTS cursor_insert_test//
CREATE PROCEDURE cursor_insert_test (IN my_param INT)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE my_id INT;

DECLARE c1 CURSOR FOR SELECT id FROM test;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

DROP TEMPORARY TABLE IF EXISTS tmp_table;
CREATE TEMPORARY TABLE tmp_table (my_int INT);

OPEN c1;
REPEAT

FETCH c1 INTO my_id;
IF NOT done THEN
	INSERT INTO tmp_table (my_int) VALUES (my_id);
END IF;
UNTIL done END REPEAT;
CLOSE c1;

SELECT * FROM tmp_table;
END
//

This does not:

DROP PROCEDURE IF EXISTS cursor_insert_test//
CREATE PROCEDURE cursor_insert_test (IN my_param INT)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE my_id INT;

DECLARE c1 CURSOR FOR SELECT id FROM test;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

DROP TEMPORARY TABLE IF EXISTS tmp_table;
CREATE TEMPORARY TABLE tmp_table (my_int INT);

IF (my_param=1) THEN
	OPEN c1;
	REPEAT
	
	FETCH c1 INTO my_id;
	IF NOT done THEN
		INSERT INTO tmp_table (my_int) VALUES (my_id);
	END IF;
	UNTIL done END REPEAT;
	CLOSE c1;
ELSE
	SELECT 'hello world';
END IF;

SELECT * FROM tmp_table;
END
//

I installed 5.0.21 and the first procedure compiled. However, I still having the same problem with the second (IF-THEN block) procedure. The error I get is:

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 'INTO tmp_table (my_int) VALUES (my_id);

It always seems to choke at the INTO after INSERT.

HTH
-c

After doing some more research, I believe this may be related to my method of entering the code. I have been using a text editor to write the procedure code and then have been copy-pasting the SQL into the terminal window. This frequently causes an error on the first attempt, but going back and copy-pasting again once or twice almost always causes it to compile. I have been formatting my cursors with tab-indents ex:

OPEN c1;
...
<blank line>
IF NOT done THEN
<tab-indent>OPEN c2;
...

When I went back and pasted the procedure I was working on line-by-line into the terminal (without tab-indents), it worked fine. In the absence of a decent SP editor, I believe the terminal should be able to handle a text copy-paste so perhaps this should be classed as a server bug. I also have not extensively tried this workaround, but I have lots of developing to do so I will try to replicate anything I can.

Can you just save the file form your editor (if it is plain text), or save what you copied during the very first attempt to plain tetx file, and upload this file to the bug report. I wonder is it really Tab or some other "whitespace" character...

example of problem (made with BBEdit 8.2.5)

Attachment: bug_ex.txt (text/plain), 1.89 KiB.

Here's a file to test.

Let me check. It is a tab in your file (hex code 09)... It looks more like mysql client related problem, if any.

Please, send the results of:

mysql --version

command.

+-----------------+
| version()       |
+-----------------+
| 5.0.21-standard | 
+-----------------+

That is a server version (results of select version(); statement executed by server). As we suspect CLIENT (mysql), please, send the results of:

mysql --version

Sorry, here it is:

mysql  Ver 14.12 Distrib 5.0.21, for apple-darwin8.2.1 (powerpc) using readline 5.0

Verified just as described (in file bug_ex.txt uploaded) with:

openxs@suse:~/dbs/5.0> bin/mysql --version
bin/mysql  Ver 14.12 Distrib 5.0.24, for suse-linux (i686) using readline 5.0

on Linux. It looks like a bug in readline (or improper usage of readline in mysql command line client).

I cannot repeat it any more, everything compiles and works.
Here are my version numbers:

mysql> select version()//
+---------------------------+
| version()                 |
+---------------------------+
| 5.0.44-valgrind-max-debug | 
+---------------------------+
1 row in set (0.00 sec)

mysql> Bye
kostja@bodhi:~/work/mysql-5.0-runtime/sql$ mysql --version
mysql  Ver 14.12 Distrib 5.0.38, for pc-linux-gnu (i486) using readline 5.2

Are you sure it's a bug in MySQL?

Is it possible this is a problem w/ tab trying to perform some auto-complete of an identifier?

Try using the "source" or "." commands instead of pasting the procedure body into the client. You may also consider using --disable-auto-rehash to see if that helps.

Please re-verify with the latest 5.1 (can't repeat)

Please, try to repeat with a newer version of mysql command line client and server, 5.0.67, and inform about the results.

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".