[Indexing] Cardinality not updated with 'analyze table x'

[zhangysh1995]

Bug Report

Basically, this reports a bug when investigating #15661

1. What did you do?

CREATE TABLE `t` (  `id` int primary key,  `a` bigint(20) DEFAULT NULL,  `b` char(20) DEFAULT NULL,  `c` datetime DEFAULT NULL,  `d` double DEFAULT NULL,  `e` json DEFAULT NULL,  `f` decimal(40,6) DEFAULT NULL,  KEY `a` (`a`),  KEY `b` (`b`),  KEY `c` (`c`),  KEY `d` (`d`),  KEY `f` (`f`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; create database correct;

mysql> show index from t;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t     |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               | YES     | NULL       |
| t     |          1 | a        |            1 | a           | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               | YES     | NULL       |
| t     |          1 | b        |            1 | b           | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               | YES     | NULL       |
| t     |          1 | c        |            1 | c           | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               | YES     | NULL       |
| t     |          1 | d        |            1 | d           | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               | YES     | NULL       |
| t     |          1 | f        |            1 | f           | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
6 rows in set (0.00 sec)

I inserted 100 rows into the table and do analyze table t:

mysql -P 4000 -h 127.0.0.1 correct < fulldb23-04-2020\ 08-44.sql fulldb23-04-2020 08-44.sql

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

mysql> analyze table t;
Query OK, 0 rows affected (0.34 sec) 

2. What did you expect to see?

The cardinality should be updated.

3. What did you see instead?

mysql> show index from t;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t     |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               | YES     | NULL       |
| t     |          1 | a        |            1 | a           | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               | YES     | NULL       |
| t     |          1 | b        |            1 | b           | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               | YES     | NULL       |
| t     |          1 | c        |            1 | c           | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               | YES     | NULL       |
| t     |          1 | d        |            1 | d           | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               | YES     | NULL       |
| t     |          1 | f        |            1 | f           | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
6 rows in set (0.00 sec)

4. What version of TiDB are you using? (tidb-server -V or run select tidb_version(); on TiDB)

mysql> select tidb_version();
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                        |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v4.0.0-rc
Git Commit Hash: 79db9e30ab8f98ac07c8ae55c66dfecc24b43d56
Git Branch: heads/refs/tags/v4.0.0-rc
UTC Build Time: 2020-04-08 07:32:25
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[SunRunAway]

@lzmhhh123 PTAL if free.

[Myshiner]

Is this issue updated.

[SunRunAway]

It's an old issue, let me hand over to @chrysan