Upper/Lower bounds of stats buckets don't do correct time zone conversion
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Bug Report
Please answer these questions before submitting your issue. Thanks!
### 1. Minimal reproduce step (Required)
Prepare a cluster with one tidb node(the issue exists for multiple tidb nodes but here we only use one tidb node for demonstration).
I execute the following commands at about 2023-03-07 16:20~16:40(time zone +8:00).
First, execute the following commands at Client A:
```
use test;
set @@time_zone = '+6:00';
set @@global.tidb_enable_auto_analyze = 0; -- disable auto analyze
create table t(a timestamp);
insert into t values (now());
insert into t values (now());
insert into t values (now());
insert into t values (now());
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
-- wait 1 min to dump stats delta in the background
analyze table t with 0 topn;
explain select * from t where a > 0; -- use the query to load column a stats to tidb mem
```
Check the following results:
```
mysql> select a from t group by a;
+---------------------+
| a |
+---------------------+
| 2023-03-07 14:24:33 |
| 2023-03-07 14:24:29 |
| 2023-03-07 14:24:31 |
| 2023-03-07 14:24:32 |
+---------------------+
4 rows in set (0.00 sec)
mysql> show stats_meta;
+---------+------------+----------------+---------------------+--------------+-----------+
| Db_name | Table_name | Partition_name | Update_time | Modify_count | Row_count |
+---------+------------+----------------+---------------------+--------------+-----------+
| test | t | | 2023-03-07 16:26:35 | 0 | 2048 |
+---------+------------+----------------+---------------------+--------------+-----------+
1 row in set (0.00 sec)
mysql> show stats_histograms;
+---------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+--------------------+-------------+-----------------+----------------+----------------+---------------+
| 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 | t | | a | 0 | 2023-03-07 16:26:35 | 4 | 0 | 8 | 0.2518301610541728 | allLoaded | 498 | 425 | 73 | 0 |
+---------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+--------------------+-------------+-----------------+----------------+----------------+---------------+
1 row in set (0.01 sec)
mysql> show stats_buckets;
+---------+------------+----------------+-------------+----------+-----------+-------+---------+---------------------+---------------------+------+
| Db_name | Table_name | Partition_name | Column_name | Is_index | Bucket_id | Count | Repeats | Lower_Bound | Upper_Bound | Ndv |
+---------+------------+----------------+-------------+----------+-----------+-------+---------+---------------------+---------------------+------+
| test | t | | a | 0 | 0 | 512 | 512 | 2023-03-07 14:24:31 | 2023-03-07 14:24:31 | 0 |
| test | t | | a | 0 | 1 | 1024 | 512 | 2023-03-07 14:24:32 | 2023-03-07 14:24:32 | 0 |
| test | t | | a | 0 | 2 | 1536 | 512 | 2023-03-07 14:24:33 | 2023-03-07 14:24:33 | 0 |
+---------+------------+----------------+-------------+----------+-----------+-------+---------+---------------------+---------------------+------+
3 rows in set (0.00 sec)
mysql> show stats_topn;
+---------+------------+----------------+-------------+----------+---------------------+-------+
| Db_name | Table_name | Partition_name | Column_name | Is_index | Value | Count |
+---------+------------+----------------+-------------+----------+---------------------+-------+
| test | t | | a | 0 | 2023-03-07 14:24:29 | 512 |
+---------+------------+----------------+-------------+----------+---------------------+-------+
1 row in set (0.00 sec)
mysql> explain analyze select * from t where a > '2023-03-07 14:20:00' and a < '2023-03-07 14:40:00';
+-------------------------+---------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------+---------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-------------------------+---------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------+---------+------+
| TableReader_7 | 1536.00 | 2048 | root | | time:3.82ms, loops:3, cop_task: {num: 5, max: 1.5ms, min: 166.1µs, avg: 723.4µs, p95: 1.5ms, tot_proc: 1ms, rpc_num: 5, rpc_time: 3.52ms, copr_cache_hit_ratio: 0.00, build_task_duration: 69.2µs, max_distsql_concurrency: 1} | data:Selection_6 | 13.4 KB | N/A |
| └─Selection_6 | 1536.00 | 2048 | cop[tikv] | | tikv_task:{proc max:1.45ms, min:134.4µs, avg: 669.4µs, p80:1.45ms, p95:1.45ms, iters:0, tasks:5} | gt(test.t.a, 2023-03-07 14:20:00.000000), lt(test.t.a, 2023-03-07 14:40:00.000000) | N/A | N/A |
| └─TableFullScan_5 | 2048.00 | 2048 | cop[tikv] | table:t | tikv_task:{proc max:1.45ms, min:134.4µs, avg: 669.4µs, p80:1.45ms, p95:1.45ms, iters:0, tasks:5} | keep order:false | N/A | N/A |
+-------------------------+---------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------+---------+------+
3 rows in set (0.00 sec)
```
Then execute the following commands at Client B:
```
use test;
set @@time_zone = '+10:00';
```
Check the following results:
```
mysql> select a from t group by a;
+---------------------+
| a |
+---------------------+
| 2023-03-07 18:24:33 |
| 2023-03-07 18:24:32 |
| 2023-03-07 18:24:31 |
| 2023-03-07 18:24:29 |
+---------------------+
4 rows in set (0.00 sec)
mysql> show stats_meta;
+---------+------------+----------------+---------------------+--------------+-----------+
| Db_name | Table_name | Partition_name | Update_time | Modify_count | Row_count |
+---------+------------+----------------+---------------------+--------------+-----------+
| test | t | | 2023-03-07 16:26:35 | 0 | 2048 |
+---------+------------+----------------+---------------------+--------------+-----------+
1 row in set (0.00 sec)
mysql> show stats_histograms;
+---------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+--------------------+-------------+-----------------+----------------+----------------+---------------+
| 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 | t | | a | 0 | 2023-03-07 16:26:35 | 4 | 0 | 8 | 0.2518301610541728 | allLoaded | 498 | 425 | 73 | 0 |
+---------+------------+----------------+-------------+----------+---------------------+----------------+------------+--------------+--------------------+-------------+-----------------+----------------+----------------+---------------+
1 row in set (0.00 sec)
mysql> show stats_buckets;
+---------+------------+----------------+-------------+----------+-----------+-------+---------+---------------------+---------------------+------+
| Db_name | Table_name | Partition_name | Column_name | Is_index | Bucket_id | Count | Repeats | Lower_Bound | Upper_Bound | Ndv |
+---------+------------+----------------+-------------+----------+-----------+-------+---------+---------------------+---------------------+------+
| test | t | | a | 0 | 0 | 512 | 512 | 2023-03-07 14:24:31 | 2023-03-07 14:24:31 | 0 |
| test | t | | a | 0 | 1 | 1024 | 512 | 2023-03-07 14:24:32 | 2023-03-07 14:24:32 | 0 |
| test | t | | a | 0 | 2 | 1536 | 512 | 2023-03-07 14:24:33 | 2023-03-07 14:24:33 | 0 |
+---------+------------+----------------+-------------+----------+-----------+-------+---------+---------------------+---------------------+------+
3 rows in set (0.00 sec)
mysql> show stats_topn;
+---------+------------+----------------+-------------+----------+---------------------+-------+
| Db_name | Table_name | Partition_name | Column_name | Is_index | Value | Count |
+---------+------------+----------------+-------------+----------+---------------------+-------+
| test | t | | a | 0 | 2023-03-07 18:24:29 | 512 |
+---------+------------+----------------+-------------+----------+---------------------+-------+
1 row in set (0.00 sec)
mysql> explain analyze select * from t where a > '2023-03-07 14:20:00' and a < '2023-03-07 14:40:00';
+-------------------------+---------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------+-----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-------------------------+---------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------+-----------+------+
| TableReader_7 | 1024.00 | 0 | root | | time:1.3ms, loops:1, cop_task: {num: 1, max: 1.23ms, proc_keys: 0, tot_proc: 1ms, rpc_num: 1, rpc_time: 1.2ms, copr_cache_hit_ratio: 0.00, build_task_duration: 8.13µs, max_distsql_concurrency: 1} | data:Selection_6 | 181 Bytes | N/A |
| └─Selection_6 | 1024.00 | 0 | cop[tikv] | | tikv_task:{time:1.18ms, loops:0} | gt(test.t.a, 2023-03-07 14:20:00.000000), lt(test.t.a, 2023-03-07 14:40:00.000000) | N/A | N/A |
| └─TableFullScan_5 | 2048.00 | 2048 | cop[tikv] | table:t | tikv_task:{time:1.18ms, loops:0} | keep order:false | N/A | N/A |
+-------------------------+---------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------+-----------+------+
3 rows in set (0.01 sec)
mysql> explain analyze select * from t where a > '2023-03-07 18:20:00' and a < '2023-03-07 18:40:00';
+-------------------------+---------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------+---------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-------------------------+---------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------+---------+------+
| TableReader_7 | 0.00 | 2048 | root | | time:3.02ms, loops:3, cop_task: {num: 5, max: 943.1µs, min: 125.3µs, avg: 568.7µs, p95: 943.1µs, rpc_num: 5, rpc_time: 2.72ms, copr_cache_hit_ratio: 0.00, build_task_duration: 10.2µs, max_distsql_concurrency: 1} | data:Selection_6 | 13.4 KB | N/A |
| └─Selection_6 | 0.00 | 2048 | cop[tikv] | | tikv_task:{proc max:908µs, min:98.2µs, avg: 516.8µs, p80:908µs, p95:908µs, iters:0, tasks:5} | gt(test.t.a, 2023-03-07 18:20:00.000000), lt(test.t.a, 2023-03-07 18:40:00.000000) | N/A | N/A |
| └─TableFullScan_5 | 2048.00 | 2048 | cop[tikv] | table:t | tikv_task:{proc max:908µs, min:98.2µs, avg: 516.8µs, p80:908µs, p95:908µs, iters:0, tasks:5} | keep order:false | N/A | N/A |
+-------------------------+---------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------+---------+------+
3 rows in set (0.01 sec)
```
Notice that at Client B, `Lower_Bound` and `Upper_Bound` of `show stats_buckets` are all like `2023-03-07 14:24:3*`, which don't do the right time zone conversion. The right `Lower_Bound` and `Upper_Bound` should be like `2023-03-07 18:24:3*` since its time zone is +10:00. By contrast, `Value` of `show stats_topn` is `2023-03-07 18:24:29`, which do the right time zone conversion.
The issue affects not only stats info display, but also cardinality estimation, which can be shown by the two `explain analyze` statements at Client B.
### 2. What did you expect to see? (Required)
Upper/Lower bounds of stats buckets do correct time zone conversion.
### 3. What did you see instead (Required)
Upper/Lower bounds of stats buckets don't do correct time zone conversion.
### 4. What is your TiDB version? (Required)
v6.6
Contributor guide
Assessment
This issue has not been assessed yet.