pingcap / pingcap/tidb

Wrong column histogram stats after reduce column length

Open
#36,774 1 comment 0 reactions 1 assignee Claimed by @xuyifangreeneyes View on GitHub
severity/moderate sig/planner type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

Wrong Column histogram stats after reduce column length.It may cause wrong estrow and estcost calculate.
### 1. Minimal reproduce step (Required)

```
create table test (id int(11) AUTO_INCREMENT primary key,name varchar(10),KEY `idx_name` (`name`));
insert into test(name) values('1');
insert into test(name) values('0');
insert into test(name) values('');
insert into test(name) select name from test where name='1';
insert into test(name) select name from test where name='1';
insert into test(name) select name from test where name='1';
insert into test(name) select name from test where name='1';
insert into test(name) select name from test where name='1';
insert into test(name) select name from test where name='1';
insert into test(name) select name from test where name='1';
insert into test(name) select name from test where name='1';
insert into test(name) select name from test where name='0';
insert into test(name) select name from test where name='0';
insert into test(name) select name from test where name='0';
insert into test(name) select name from test where name='0';
insert into test(name) select name from test where name='0';
insert into test(name) select name from test where name='0';
insert into test(name) select name from test where name='0';
insert into test(name) select name from test where name='';
insert into test(name) select name from test where name='';
insert into test(name) select name from test where name='';
insert into test(name) select name from test where name='';
insert into test(name) select name from test where name='';
insert into test(name) select name from test where name='';

analyze table test.test;
show stats_histograms where table_name='test';
+---------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+-------------+
| Db_name | Table_name | Partition_name | Column_name | Is_index | Update_time | Distinct_count | Null_count | Avg_col_size | Correlation |
+---------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+-------------+
| test | test | | id | 0 | 2022-08-01 17:34:06 | 448 | 0 | 8 | 0 |
| test | test | | idx_name | 1 | 2022-08-01 17:34:06 | 3 | 0 | 0 | 0 |
+---------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+-------------+
2 rows in set (0.01 sec)

alter table test modify column name varchar(9);
Query OK, 0 rows affected (5.48 sec)
```

### 2. What did you expect to see? (Required)
```
show stats_histograms where table_name='test';
+---------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+-------------+
| Db_name | Table_name | Partition_name | Column_name | Is_index | Update_time | Distinct_count | Null_count | Avg_col_size | Correlation |
+---------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+-------------+
| test | test | | id | 0 | 2022-08-01 17:34:06 | 448 | 0 | 8 | 0 |
| test | test | | name | 0 | 2022-08-01 17:34:36 | 3 | 0 | 0 | 0 |
| test | test | | idx_name | 1 | 2022-08-01 17:34:06 | 3 | 0 | 0 | 0 |
+---------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+-------------+
3 rows in set (0.00 sec)
```
### 3. What did you see instead (Required)
```
show stats_histograms where table_name='test';
+---------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+-------------+
| Db_name | Table_name | Partition_name | Column_name | Is_index | Update_time | Distinct_count | Null_count | Avg_col_size | Correlation |
+---------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+-------------+
| test | test | | id | 0 | 2022-08-01 17:34:06 | 448 | 0 | 8 | 0 |
| test | test | | name | 0 | 2022-08-01 17:34:36 | 0 | 448 | 0 | 0 |
| test | test | | idx_name | 1 | 2022-08-01 17:34:06 | 3 | 0 | 0 | 0 |
+---------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+-------------+
```
3 rows in set (0.00 sec)
### 4. What is your TiDB version? (Required)
v5.x

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.