Bug #77687 Cursor move to end & 'SELECT INTO ...' have no result are the same error code
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.
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.
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.