Bug #77687 Cursor move to end & 'SELECT INTO ...' have no result are the same error code
Submitted: 11 Jul 2015 7:45 Modified: 14 Sep 2019 21:25
Reporter: Hotpu Technology Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Errors Severity:S3 (Non-critical)
Version:5.6/5.7/8.0 OS:CentOS
Assigned to: CPU Architecture:Any
Tags: cursor, same error code, SELECT INTO variables

[11 Jul 2015 7:45] Hotpu Technology
Description:
Cursor move to end & 'SELECT INTO variables' have no result are the same error code

How to repeat:
root@localhost : hotdb 03:32:44> SHOW CREATE TABLE h1;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                               |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| h1    | CREATE TABLE `h1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8 |

root@localhost : hotdb 03:34:51> SELECT * FROM h1;
+----+--------+
| id | name   |
+----+--------+
|  1 | MySQL  |
|  2 | Oracle |
|  3 | JAVA   |
|  4 | PHP    |
|  5 | Python |
| 20 | HTML5  |
| 21 | SWIFT  |
+----+--------+
7 rows in set (0.00 sec)

DELIMITER $$
DROP PROCEDURE IF EXISTS proc_select_no_data $$
CREATE PROCEDURE  proc_select_no_data()
BEGIN

DECLARE i_num INT DEFAULT 0;
DECLARE i_cur_num INT DEFAULT 0;

DECLARE i_error TINYINT DEFAULT 0;
DECLARE i_while TINYINT DEFAULT 0;

DECLARE cur_test CURSOR FOR SELECT ID FROM h1 WHERE ID<10;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'  SET i_error=2;  
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION  SET i_error=1;

SET i_while=2;
OPEN cur_test;

WHILE i_while>0 
DO
   FETCH cur_test INTO i_cur_num;

	WHILE i_error<>2 
	DO
	  SELECT id INTO  i_num FROM h1 WHERE name='asdfdsfadfdf' limit 1;

	  SELECT  CONCAT('while: ',i_while,' sqlexception: ',i_error,'  i_num: ',i_num);

	END WHILE;

  SELECT CONCAT(REPEAT('*',i_while*i_cur_num*10),':',i_while,':',i_cur_num);

  SET i_while=i_while-1;

END WHILE;

CLOSE cur_test;

END $$

DELIMITER ;

when I first execute the SQL like 'SELECT id INTO i_num' in the second WHILE statement,but the table h1 have no result to meet the condition of   name='asdfdsfadfdf' and the SQL statement trigger the warning like :
Warning (Code 1329): No data - zero rows fetched, selected, or processed

this warning code the same as the error code of Cursor move to end

root@localhost : hotdb 04:03:59> call  proc_select_no_data();
+---------------------------------------------------------------------------+
| CONCAT('while: ',i_while,' sqlexception: ',i_error,'  i_num: ',i_num)   |
+---------------------------------------------------------------------------+
| while: 2 sqlexception: 2  i_num: 0                                      |
+---------------------------------------------------------------------------+
1 row in set (0.00 sec)

+--------------------------------------------------------------------+
| CONCAT(REPEAT('*',i_while*i_cur_num*10),':',i_while,':',i_cur_num) |
+--------------------------------------------------------------------+
| ********************:2:1                                           |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)

+--------------------------------------------------------------------+
| CONCAT(REPEAT('*',i_while*i_cur_num*10),':',i_while,':',i_cur_num) |
+--------------------------------------------------------------------+
| ********************:1:2                                           |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Suggested fix:
Suggest to separate the error code of Cursor move to end  and 'SELECT INTO variables' have no result.
[14 Sep 2019 21:25] MySQL Verification Team
Thank you for the bug report.