Bug #108639 Value of row_count() does not reset after a DROP TABLE in a procedure
Submitted: 29 Sep 1:15 Modified: 29 Sep 6:14
Reporter: Phong Dinh Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:8.0.29, 8.0.30 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[29 Sep 1:15] Phong Dinh
Description:
The value of row_count() does not reset after a DROP TABLE in a procedure. This value increase after each iteration of the procedure.

mysql> delimiter //
mysql> CREATE DEFINER='root'@'%' PROCEDURE test_procedure()
    -> begin
    -> drop table if exists tmptb;
    -> create table tmptb (primary key (id)) engine=innodb as select * from tbtest;
    -> select row_count();
    -> select count(*) from tbtest;
    -> drop  table if exists tmptb;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> call test_procedure;
+-------------+
| row_count() |
+-------------+
|        1000 |
+-------------+
1 row in set (0.04 sec)

+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.04 sec)

Query OK, 0 rows affected (0.05 sec)

mysql> call test_procedure;
+-------------+
| row_count() |
+-------------+
|        2000 |
+-------------+
1 row in set (0.03 sec)

+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.05 sec)

Query OK, 0 rows affected (0.07 sec)

mysql> call test_procedure;
+-------------+
| row_count() |
+-------------+
|        3000 |
+-------------+
1 row in set (0.02 sec)

+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.10 sec)

If execute these statements without a Procedure, the value of row_count() is reset after each DROP TABLE

mysql> drop table if exists tmptb;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table tmptb (primary key (id)) engine=innodb as select * from tbtest;
Query OK, 1000 rows affected (0.06 sec)
Records: 1000  Duplicates: 0  Warnings: 0

mysql> select row_count();
+-------------+
| row_count() |
+-------------+
|        1000 |
+-------------+
1 row in set (0.00 sec)

mysql> select count(*) from tbtest;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.02 sec)

mysql> drop  table if exists tmptb;
Query OK, 0 rows affected (0.00 sec)

mysql> create table tmptb (primary key (id)) engine=innodb as select * from tbtest;
Query OK, 1000 rows affected (0.02 sec)
Records: 1000  Duplicates: 0  Warnings: 0

mysql> select row_count();
+-------------+
| row_count() |
+-------------+
|        1000 |
+-------------+
1 row in set (0.00 sec)

mysql> select count(*) from tbtest;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

mysql> drop  table if exists tmptb;
Query OK, 0 rows affected (0.01 sec)

mysql> select row_count();
+-------------+
| row_count() |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

How to repeat:
Create this table and this procedure
mysql> show create table tbtest\G
*************************** 1. row ***************************
       Table: tbtest
Create Table: CREATE TABLE `tbtest` (
  `id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
show create procedure test_procedure\G
*************************** 1. row ***************************
           Procedure: test_procedure
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`%` PROCEDURE `test_procedure`()
begin
drop table if exists tmptb;
create table tmptb (primary key (id)) engine=innodb as select * from tbtest;
select row_count();
select count(*) from tbtest;
drop  table if exists tmptb;
end
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

Then call the procedure multiple times in the same session.

Suggested fix:
Reset the value of row_count() after each drop table when it is called in a procedure
[29 Sep 6:14] MySQL Verification Team
Hello Phong Dinh,

Thank you for the report and feedback.

regards,
Umesh