MySQL Bugs: #3437: Stored procedure with nested IF statements causes crash

Bug #3437	Stored procedure with nested IF statements causes crash
Submitted:	11 Apr 2004 7:15	Modified:	18 Apr 2004 6:45
Reporter:	Matt Chatterley	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.0a-alpha/5.0.1	OS:	Windows (Windows 2000 Professional)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Using mysqld-opt on Windows 2000 Professional (5.0.0a-alpha), and the stored procedure shown below is created and called, the error: The instruction at "0x00000000" referenced memory at "0x00000000". The memory could not be "read"

Is displayed, and the server shuts down 'unexpectedly'.

I have tried different permutations of the procedure, and it is the addition of a 'nested' if statement which triggers the problem - if this is removed, the code will execute with no problems and give the expected result.

All tables referenced are InnoDB, and are simple tables with two columns (an auto increment primary key and a unique indexed value column of type VARCHAR(6)).

How to repeat:
From the command line:

delimiter //
CREATE PROCEDURE name_test (OUT sFragment VARCHAR(6), OUT iRand INT)
BEGIN
DECLARE bContinue INT;
--DECLARE iRand INT;

SELECT 1 INTO bContinue;

WHILE bContinue = 1 DO

SELECT CAST((RAND() * 100)/33 AS UNSIGNED) INTO iRand;

IF iRand = 0 
THEN

SELECT Fragment INTO sFragment FROM namefragmentvowel ORDER BY RAND() LIMIT 1;

ELSE IF RAND = 1 THEN
SELECT Fragment INTO sFragment FROM namefragmentconsonant ORDER BY RAND() LIMIT 1;

END IF;
END IF;

SELECT 0 INTO bContinue;

END WHILE;
END
//

Then from MySQLCC:

call name_test(@sTest, @iRand);
select @sTest AS frag, @iRand AS rng;

A short addition. I tried restructuring my 'IF' to be neater, using 'ELSEIF' and only one 'END IF', as below. However, this still causes the server to bomb out.

CREATE PROCEDURE `name_test`(OUT sFragment VARCHAR(6), OUT iRand INT)
BEGIN
DECLARE bContinue INT;
SELECT 1 INTO bContinue;
WHILE bContinue = 1 DO
SELECT CAST((RAND() * 100)/33 AS UNSIGNED) INTO iRand;
IF iRand = 0 
THEN
SELECT Fragment INTO sFragment FROM namefragmentvowel ORDER BY RAND() LIMIT 1;
ELSEIF RAND = 1 THEN
SELECT Fragment INTO sFragment FROM namefragmentconsonant ORDER BY RAND() LIMIT 1;
ELSE
SELECT Fragment INTO sFragment FROM nameFragmentMixed ORDER BY RAND() LIMIT 1;
END IF;
SELECT 0 INTO bContinue;
END WHILE;
END

Thank you for the bug report I was able to repeat with a server built
with latest source 5.0 BK tree.

Call stack:

 	a5a5a5a5()	
>	mysqld.exe!Arg_comparator::compare()  Line 63 + 0x10	C++
 	mysqld.exe!Item_func_eq::val_int()  Line 495 + 0xb	C++
 	mysqld.exe!sp_instr_jump_if_not::execute(THD * thd=0x02f02650, unsigned int * nextp=0x03e0f014)  Line 1103 + 0xb	C++
 	mysqld.exe!sp_head::execute(THD * thd=0x02f02650)  Line 404 + 0x19	C++
 	mysqld.exe!sp_head::execute_procedure(THD * thd=0x02f02650, List<Item> * args=0x02f02988)  Line 589 + 0xf	C++
 	mysqld.exe!mysql_execute_command(THD * thd=0x02f02650)  Line 3475 + 0x19	C++
 	mysqld.exe!mysql_parse(THD * thd=0x02f02650, char * inBuf=0x02f09260, unsigned int length=30)  Line 4150 + 0x9	C++
 	mysqld.exe!dispatch_command(enum_server_command command=COM_QUERY, THD * thd=0x02f02650, char * packet=0x02f051f9, unsigned int packet_length=31)  Line 1411 + 0x1d	C++
 	mysqld.exe!do_command(THD * thd=0x02f02650)  Line 1241 + 0x31	C++
 	mysqld.exe!handle_one_connection(void * arg=0x02f02650)  Line 1001 + 0x9	C++
 	mysqld.exe!pthread_start(void * param=0x00d4a8e8)  Line 63 + 0x7	C
 	mysqld.exe!_threadstart(void * ptd=0x00e555c8)  Line 173 + 0xd	C
 	kernel32.dll!77e6d33b()

This is actually the same as BUG#2653. The problem is the
reference to an undeclared variable "RAND" in the second
IF statement. (Presumably is should be "iRand").