MySQL Bugs: #24575: incorrect table_rows value in information

Bug #24575	incorrect table_rows value in information_schema.tables for a merged table
Submitted:	24 Nov 2006 15:05	Modified:	6 Dec 2006 12:01
Reporter:	Corrado Pandiani	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S2 (Serious)
Version:	5.0.32-BK,5.0.20 / 5.1.11	OS:	Linux (debian Linux / Mac OS)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	information_schema, merge, table_rows

Description:
In some situation (as described below in "how to repeat") the table_rows column in the information_schema.tables table is not computed correctly.
The problem arise only for tables included in a merge table.

It seems that if you issue a select statement on a "merged table" the following  insert statement does not update the metadata table_rows.
   
 

How to repeat:
First of all let's try the example showing the expected behavior

mysql> create table t1 (a int);
Query OK, 0 rows affected (0.00 sec)

mysql> create table t2 (a int);
Query OK, 0 rows affected (0.01 sec)

mysql> create table t (a int) engine=merge union=(t1,t2) insert_method=last;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t values(1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into t1 values(3),(4);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select table_name,table_rows from information_schema.tables where table_name in ('t1','t2','t');
+------------+------------+
| table_name | table_rows |
+------------+------------+
| t          |          4 | 
| t1         |          2 | 
| t2         |          2 | 
+------------+------------+
3 rows in set (0.01 sec)

mysql> insert into t values(5);
Query OK, 1 row affected (0.00 sec)

mysql> select table_name,table_rows from information_schema.tables where table_name in ('t1','t2','t');
+------------+------------+
| table_name | table_rows |
+------------+------------+
| t          |          5 | 
| t1         |          2 | 
| t2         |          3 | 
+------------+------------+
3 rows in set (0.01 sec)

let's try now the same example issuing a select statement before inserting the value 5 in the merge table 

mysql> create table t1 (a int);
Query OK, 0 rows affected (0.00 sec)

mysql> create table t2 (a int);
Query OK, 0 rows affected (0.00 sec)

mysql> create table t (a int) engine=merge union=(t1,t2) insert_method=last;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values(1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into t1 values(3),(4);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t2;
+------+
| a    |
+------+
|    1 | 
|    2 | 
+------+
2 rows in set (0.00 sec)

mysql> insert into t values(5);
Query OK, 1 row affected (0.00 sec)

mysql> select table_name,table_rows from information_schema.tables where table_name in ('t1','t2','t');
+------------+------------+
| table_name | table_rows |
+------------+------------+
| t          |          5 | 
| t1         |          2 | 
| t2         |          2 | 
+------------+------------+
3 rows in set (0.00 sec)

ooppss!!  2+2=5 ???

now, if you issue a select statement on t2, table_rows will be updated to the right value

mysql> select * from t2;
+------+
| a    |
+------+
|    1 | 
|    2 | 
|    5 | 
+------+
3 rows in set (0.01 sec)

mysql> select table_name,table_rows from information_schema.tables where table_name in ('t1','t2','t');
+------------+------------+
| table_name | table_rows |
+------------+------------+
| t          |          5 | 
| t1         |          2 | 
| t2         |          3 | 
+------------+------------+
3 rows in set (0.01 sec)

Thank you for a bug report. Verified just as described with 5.0.32-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot s
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.32-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table t1 (a int);
Query OK, 0 rows affected (0.03 sec)

mysql> create table t2 (a int);
Query OK, 0 rows affected (0.01 sec)

mysql> create table t (a int) engine=merge union=(t1,t2) insert_method=last;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t values(1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into t1 values(3),(4);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t2;
+------+
| a    |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql>  insert into t values(5);
Query OK, 1 row affected (0.00 sec)

mysql> select table_name,table_rows from information_schema.tables where
    -> table_name in ('t1','t2','t') and table_schema='s';
+------------+------------+
| table_name | table_rows |
+------------+------------+
| t          |          5 |
| t1         |          2 |
| t2         |          2 |
+------------+------------+
3 rows in set (0.06 sec)

mysql> select * from t2;
+------+
| a    |
+------+
|    1 |
|    2 |
|    5 |
+------+
3 rows in set (0.00 sec)

mysql> select table_name,table_rows from information_schema.tables where table_
name in ('t1','t2','t') and table_schema='s';
+------------+------------+
| table_name | table_rows |
+------------+------------+
| t          |          5 |
| t1         |          2 |
| t2         |          3 |
+------------+------------+
3 rows in set (0.01 sec)

Dup of bug#23526.