MySQL Bugs: #5763: In a stored procedure, a FETCH of a NULL value results in a hard crash of Mysql

Bug #5763	In a stored procedure, a FETCH of a NULL value results in a hard crash of Mysql
Submitted:	27 Sep 2004 13:23	Modified:	20 Oct 2004 16:02
Reporter:	Channa Wijesinghe	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.1	OS:	Windows (Windows XP, Linux)
Assigned to:	Per-Erik Martin	CPU Architecture:	Any

Description:
In a stored procedure, a FETCH of a null value results in a hard crash of Mysqld Server. 

How to repeat:
I created the table 'usageInfo' by grabbing a majority of my data from a pre-existing Access database. First (In Access 2002) I saved a table as a tab delimited text file. Then I used a LOAD DATA LOCAL INFILE to populate the MySql table with the data from the Access database. To get rid of blank entries I ran these lines of code:

Update USAGEINFO set actionValue_4=NULL Where actionValue_4='';
Update USAGEINFO set actionValue_5=NULL Where actionValue_5='';

Next I started to create my Stored Procedure...
Sample code:
Delimiter //
CREATE PROCEDURE ProdFreq()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
  DECLARE cur1 CURSOR FOR SELECT actionValue_4,actionValue_5,count(*) FROM usageInfo Group By ActionValue_4;
  Declare Clicks INT;
  DECLARE name varChar(60);
  DECLARE ID varChar(60);

  CREATE table temp(action_Name varChar(60),action_ID varChar(60), Frequency INT); 
  open cur1;
  
  Repeat
    FETCH cur1 INTO name,ID,clicks;
    IF NOT done THEN
       INSERT INTO temp VALUES (name,ID,clicks);
    END IF;     
  until done END Repeat;

  Close cur1;
END//
Call ProdFreq()//

I tried many different code senarios, but I always get a crash during the FETCH when 'name' is a null value. I then did this (just to get rid of the Null values entirely):  

Update USAGEINFO set actionValue_4='---' Where actionValue_4=NULL;
Update USAGEINFO set actionValue_5='---' Where actionValue_5=NULL;
 
And the stored procedure works fine now, but as you can see this is work around.

Thanks for any help you can give me.

I had to modify the procedure code a bit as it complained about 
declaration order. Below is a self contained script reproducing the problem:

Delimiter //
DROP TABLE IF EXISTS usageInfo //
CREATE TABLE usageInfo(actionValue_4 varchar(32), actionValue_5 varchar(32)) //

DROP PROCEDURE IF EXISTS ProdFreq //
CREATE PROCEDURE ProdFreq()
BEGIN
  DECLARE done INT DEFAULT 0;
  Declare Clicks INT;
  DECLARE name varChar(60);
  DECLARE ID varChar(60);
  DECLARE cur1 CURSOR FOR SELECT actionValue_4,actionValue_5,count(*) FROM usageInfo Group By ActionValue_4;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  DROP TABLE IF EXISTS temp;

  CREATE table temp(action_Name varChar(60),action_ID varChar(60), Frequency INT); 

  open cur1;
  
  Repeat
    FETCH cur1 INTO name,ID,clicks;
    IF NOT done THEN
       INSERT INTO temp VALUES (name,ID,clicks);
    END IF;     
  until done END Repeat;

  Close cur1;
END//

INSERT INTO usageInfo values('a', 'a') //
INSERT INTO usageInfo values('a', 'b') //
INSERT INTO usageInfo values('b', 'a') //
INSERT INTO usageInfo values('b', 'b') //

Call ProdFreq()//

INSERT INTO usageInfo values(NULL, NULL) //

Call ProdFreq()//

I failed to repeat this in the current source build (on linux).

Problem: FETCH (STORED PROCEDURE) a row with columns that contains a "NULL"-Value
MySQL-Error: ERROR 2013 (HY000): Lost connection to MySQL server during query
MySQL-Error: ERROR 2006 (HY000): MySQL server has gone away
MySQL-Version: 5.0.1-alpha-Max-log
OS: Linux

Simple SQL-Code:

DROP TABLE IF EXISTS NULLFetchTable;

CREATE TABLE NULLFetchTable (
  A      INTEGER,
  B      VARCHAR(5));

DROP PROCEDURE IF EXISTS NULLFetch;

delimiter //

CREATE PROCEDURE NULLFetch ()
BEGIN
  DECLARE iReady     INTEGER DEFAULT 0;
  DECLARE iA         INTEGER;
  DECLARE iB         VARCHAR(5);

  DECLARE Cursor1 CURSOR FOR
    SELECT A, B
      FROM NULLFetchTable;

  OPEN Cursor1;
  FETCH Cursor1 INTO iA, iB;
  CLOSE Cursor1;
END;
//

delimiter ;

TRUNCATE TABLE NULLFetchTable;
INSERT INTO NULLFetchTable (A, B) VALUES (1, '2');

CALL NULLFetch();

TRUNCATE TABLE NULLFetchTable;
INSERT INTO NULLFetchTable (A, B) VALUES (1, NULL);

CALL NULLFetch();