Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

wrong NDV in the global stats when to disable async-merge-global-stats #53752

Closed
hawkingrei opened this issue Jun 3, 2024 · 0 comments · Fixed by #53762
Closed

wrong NDV in the global stats when to disable async-merge-global-stats #53752

hawkingrei opened this issue Jun 3, 2024 · 0 comments · Fixed by #53762

Comments

@hawkingrei
Copy link
Member

hawkingrei commented Jun 3, 2024

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

set @@tidb_enable_async_merge_global_stats=OFF;
CREATE TABLE employees  (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    fname VARCHAR(25) NOT NULL,
    lname VARCHAR(25) NOT NULL,
    store_id INT NOT NULL,
    department_id INT NOT NULL
)

PARTITION BY RANGE(id)  (
    PARTITION p0 VALUES LESS THAN (5),
    PARTITION p1 VALUES LESS THAN (10),
    PARTITION p2 VALUES LESS THAN (15),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);



INSERT INTO employees(FNAME,LNAME,STORE_ID,DEPARTMENT_ID) VALUES
    ('Bob', 'Taylor', 3, 2), ('Frank', 'Williams', 1, 2),
    ('Ellen', 'Johnson', 3, 4), ('Jim', 'Smith', 2, 4),
    ('Mary', 'Jones', 1, 1), ('Linda', 'Black', 2, 3),
    ('Ed', 'Jones', 2, 1), ('June', 'Wilson', 3, 1),
    ('Andy', 'Smith', 1, 3), ('Lou', 'Waters', 2, 4),
    ('Jill', 'Stone', 1, 4), ('Roger', 'White', 3, 2),
    ('Howard', 'Andrews', 1, 2), ('Fred', 'Goldberg', 3, 3),
    ('Barbara', 'Brown', 2, 3), ('Alice', 'Rogers', 2, 2),
    ('Mark', 'Morgan', 3, 3), ('Karen', 'Cole', 3, 2);

ANALYZE TABLE employees;

select * from employees;

SHOW STATS_HISTOGRAMS WHERE TABLE_NAME='employees';

alter table employees truncate   partition p0 ; 

select * from employees;

SHOW STATS_HISTOGRAMS WHERE TABLE_NAME='employees';



analyze table employees partition p3;

select * from employees;

SHOW STATS_HISTOGRAMS WHERE TABLE_NAME='employees'

2. What did you expect to see? (Required)

+---------+------------+----------------+---------------+----------+---------------------+----------------+------------+--------------+-------------+-------------+-----------------+----------------+----------------+---------------+
| Db_name | Table_name | Partition_name | Column_name   | Is_index | Update_time         | Distinct_count | Null_count | Avg_col_size | Correlation | Load_status | Total_mem_usage | Hist_mem_usage | Topn_mem_usage | Cms_mem_usage |
+---------+------------+----------------+---------------+----------+---------------------+----------------+------------+--------------+-------------+-------------+-----------------+----------------+----------------+---------------+
| test    | employees  | global         | id            | 0        | 2024-06-03 14:36:02 | 14             | 0          | 8.0          | 0.0         | allEvicted  | 0               | 0              | 0              | 0             |
| test    | employees  | global         | fname         | 0        | 2024-06-03 14:36:02 | 14             | 0          | 5.29         | 0.0         | allEvicted  | 0               | 0              | 0              | 0             |
| test    | employees  | global         | lname         | 0        | 2024-06-03 14:36:02 | 13             | 0          | 6.63         | 0.0         | allEvicted  | 0               | 0              | 0              | 0             |
| test    | employees  | global         | store_id      | 0        | 2024-06-03 14:36:02 | 3              | 0          | 5.08         | 0.0         | allEvicted  | 0               | 0              | 0              | 0             |
| test    | employees  | global         | department_id | 0        | 2024-06-03 14:36:02 | 4              | 0          | 5.08         | 0.0         | allEvicted  | 0               | 0              | 0              | 0             |
+---------+------------+----------------+---------------+----------+---------------------+----------------+------------+--------------+-------------+-------------+-----------------+----------------+----------------+---------------+

3. What did you see instead (Required)

+---------+------------+----------------+---------------+----------+---------------------+----------------+------------+--------------+-------------+-------------+-----------------+----------------+----------------+---------------+
| Db_name | Table_name | Partition_name | Column_name   | Is_index | Update_time         | Distinct_count | Null_count | Avg_col_size | Correlation | Load_status | Total_mem_usage | Hist_mem_usage | Topn_mem_usage | Cms_mem_usage |
+---------+------------+----------------+---------------+----------+---------------------+----------------+------------+--------------+-------------+-------------+-----------------+----------------+----------------+---------------+
| test    | employees  | global         | id            |        0 | 2024-06-03 15:04:21 |              0 |          0 |            8 |           0 | allEvicted  |               0 |              0 |              0 |             0 |
| test    | employees  | global         | fname         |        0 | 2024-06-03 15:04:21 |              0 |          0 |         5.29 |           0 | allEvicted  |               0 |              0 |              0 |             0 |
| test    | employees  | global         | lname         |        0 | 2024-06-03 15:04:21 |              0 |          0 |         6.63 |           0 | allEvicted  |               0 |              0 |              0 |             0 |
| test    | employees  | global         | store_id      |        0 | 2024-06-03 15:04:21 |              0 |          0 |         5.08 |           0 | allEvicted  |               0 |              0 |              0 |             0 |
| test    | employees  | global         | department_id |        0 | 2024-06-03 15:04:21 |              0 |          0 |         5.08 |           0 | allEvicted  |               0 |              0 |              0 |             0 |
+---------+------------+----------------+---------------+----------+---------------------+----------------+------------+--------------+-------------+-------------+-----------------+----------------+----------------+---------------+
5 rows in set (0.00 sec)

4. What is your TiDB version? (Required)

master

@hawkingrei hawkingrei added type/bug The issue is confirmed as a bug. severity/major labels Jun 3, 2024
@hawkingrei hawkingrei self-assigned this Jun 3, 2024
ti-chi-bot bot pushed a commit that referenced this issue Jun 4, 2024
ti-chi-bot bot pushed a commit that referenced this issue Jun 5, 2024
ti-chi-bot bot pushed a commit that referenced this issue Jun 5, 2024
ti-chi-bot bot pushed a commit that referenced this issue Jun 7, 2024
hawkingrei pushed a commit to hawkingrei/tidb that referenced this issue Jul 1, 2024
hawkingrei pushed a commit to hawkingrei/tidb that referenced this issue Jul 1, 2024
ti-chi-bot bot pushed a commit that referenced this issue Sep 2, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
2 participants