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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.040 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.