Bug #94891 that maybe something wrong in information_schema when use subpartition

• Submitted: 3 Apr 2019 13:53
• Modified: 4 Apr 2019 14:28
• Reporter: YuWang Wei
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 8.0.12
• OS: Any
• CPU Architecture: Any

[3 Apr 2019 13:53] YuWang Wei

Description:
mysql> show create table test;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                          |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `message` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (`id`)
SUBPARTITION BY HASH (`id`)
SUBPARTITIONS 5
(PARTITION p0 VALUES LESS THAN (10) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (20) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from test order by id;
+----+---------+
| id | message |
+----+---------+
|  1 | hhh     |
|  2 | hhh     |
|  3 | hhh     |
|  4 | hhh     |
|  5 | hhh     |
|  6 | hhh     |
|  7 | hhh     |
|  8 | hhh     |
|  9 | hhh     |
| 10 | hhh     |
| 11 | hhh     |
| 12 | hhh     |
| 13 | hhh     |
| 14 | hhh     |
| 15 | hhh     |
| 16 | hhh     |
| 17 | hhh     |
| 18 | hhh     |
| 19 | hhh     |
| 20 | hhh     |
| 21 | hhh     |
| 22 | hhh     |
| 23 | hhh     |
| 24 | hhh     |
| 25 | hhh     |
| 26 | hhh     |
| 27 | hhh     |
| 28 | hhh     |
| 29 | hhh     |
| 30 | hhh     |
+----+---------+
30 rows in set (0.01 sec)

mysql> explain select * from test where id = 5;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test  | p0_p0sp0   | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> select PARTITION_NAME,SUBPARTITION_NAME,PARTITION_DESCRIPTION ,TABLE_ROWS from information_schema.PARTITIONS where TABLE_SCHEMA = database() and TABLE_NAME = 'test';
+----------------+-------------------+-----------------------+------------+
| PARTITION_NAME | SUBPARTITION_NAME | PARTITION_DESCRIPTION | TABLE_ROWS |
+----------------+-------------------+-----------------------+------------+
| p0             | p0sp0             | 10                    |          0 |
| p0             | p0sp1             | 10                    |          2 |
| p0             | p0sp2             | 10                    |          2 |
| p0             | p0sp3             | 10                    |          2 |
| p0             | p0sp4             | 10                    |          2 |
| p1             | p1sp0             | 20                    |          2 |
| p1             | p1sp1             | 20                    |          2 |
| p1             | p1sp2             | 20                    |          2 |
| p1             | p1sp3             | 20                    |          2 |
| p1             | p1sp4             | 20                    |          2 |
| p2             | p2sp0             | MAXVALUE              |          3 |
| p2             | p2sp1             | MAXVALUE              |          2 |
| p2             | p2sp2             | MAXVALUE              |          2 |
| p2             | p2sp3             | MAXVALUE              |          2 |
| p2             | p2sp4             | MAXVALUE              |          2 |
+----------------+-------------------+-----------------------+------------+
15 rows in set (0.00 sec)

so!maybe the first line which SUBPARTITION_NAME is p0sp0 the table_rows is 1(when id = 5)?

How to repeat:
see above.

[4 Apr 2019 12:47] MySQL Verification Team

Hi,

Thank you for your bug report.

I wish you could explain to me what exactly is the bug here ???

EXPLAIN shows that index is used for searches. Regarding the partitions they are named upon an algorithm used internally. That algorithm is sufficiently described in our Reference Manual.

[4 Apr 2019 14:28] YuWang Wei

I am confused about the "TABLE_ROWS" column of the table "information_schema.PARTITIONS" because the first line should be 1 (actually 0). When id = 5, the logic according to the algorithm should be assigned to the sub-partition "p0sp0", which is also confirmed by "explain".

I thought this was a bug, but I ended up with a description of the "TABLE_ROWS" column in the documentation: "For partitioned InnoDB tables, the row count given in the TABLE_ROWS column is only an estimated value used in SQL optimization, and may not always Be exact."

I am sorry to have given you extra work.

[4 Apr 2019 14:32] MySQL Verification Team

HI Wei,

There is no need to apologise at all !!!!

You are truly welcome !

Have a nice weekend.