## Bug Report
Please answer these questions before submitting your issue. Tha…nks!
### 1. Minimal reproduce step (Required)
1. Create a TiDB cluster with nightly image.
2. Load TPCDS data, sf=50
3. Run Query_63
### 2. What did you expect to see? (Required)
This is the output from mysql client
```
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1005
Server version: 5.7.25-TiDB-v5.3.0-alpha-1053-g6e70be97c TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
root@tpcds50>explain analyze
-> select /* plan test 4*/ *
-> from (select i_manager_id
-> ,sum(ss_sales_price) sum_sales
-> ,avg(sum(ss_sales_price)) over (partition by i_manager_id) avg_monthly_sales
-> from item
-> ,store_sales
-> ,date_dim
-> ,store
-> where ss_item_sk = i_item_sk
-> and ss_sold_date_sk = d_date_sk
-> and ss_store_sk = s_store_sk
-> and d_month_seq in (1212,1212+1,1212+2,1212+3,1212+4,1212+5,1212+6,1212+7,1212+8,1212+9,1212+10,1212+11)
-> and (( i_category in ('Books','Children','Electronics')
-> and i_class in ('personal','portable','reference','self-help')
-> and i_brand in ('scholaramalgamalg #14','scholaramalgamalg #7',
->
Display all 1641 possibilities? (y or n)
-> 'exportiunivamalg #9','scholaramalgamalg #9'))
-> or( i_category in ('Women','Music','Men')
-> and i_class in ('accessories','classical','fragrances','pants')
-> and i_brand in ('amalgimporto #1','edu packscholar #1','exportiimporto #1',
->
Display all 1641 possibilities? (y or n)
-> 'importoamalg #1')))
-> group by i_manager_id, d_moy) tmp1
-> where case when avg_monthly_sales > 0 then abs (sum_sales - avg_monthly_sales) / avg_monthly_sales else null end > 0.1
-> order by i_manager_id
-> ,avg_monthly_sales
-> ,sum_sales
-> limit 100;
+--------------------------------------------------+-----------+---------+--------------+----------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+--------------------------------------------------+-----------+---------+--------------+----------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
| Projection_24 | 0.80 | 100 | root | | time:1m9s, loops:2, Concurrency:OFF | tpcds50.item.i_manager_id, Column#104, Column#106 | 9.44 KB | N/A |
| └─TopN_27 | 0.80 | 100 | root | | time:1m9s, loops:2 | tpcds50.item.i_manager_id, Column#106, Column#104, offset:0, count:100 | 10.2 KB | N/A |
| └─Selection_32 | 0.80 | 266 | root | | time:1m9s, loops:3 | gt(case(gt(Column#106, 0), div(abs(minus(Column#104, Column#106)), Column#106), NULL), 0.1) | 28.1 KB | N/A |
| └─Window_33 | 1.00 | 276 | root | | time:1m9s, loops:3 | avg(Column#104)->Column#106 over(partition by tpcds50.item.i_manager_id) | N/A | N/A |
| └─Sort_150 | 1.00 | 276 | root | | time:1m9s, loops:2 | tpcds50.item.i_manager_id | 16.1 KB | 0 Bytes |
| └─HashAgg_34 | 1.00 | 276 | root | | time:1m9s, loops:6, partial_worker:{wall_time:1m8.98892105s, concurrency:5, task_num:25, tot_wait:5m44.93899803s, tot_exec:4.907913ms, tot_time:5m44.944109003s, max:1m8.988882745s, p95:1m8.988882745s}, final_worker:{wall_time:1m8.989111313s, concurrency:5, task_num:25, tot_wait:5m44.944067601s, tot_exec:1.199022ms, tot_time:5m44.945273426s, max:1m8.989081408s, p95:1m8.989081408s} | group by:tpcds50.date_dim.d_moy, tpcds50.item.i_manager_id, funcs:sum(tpcds50.store_sales.ss_sales_price)->Column#104, funcs:firstrow(tpcds50.item.i_manager_id)->tpcds50.item.i_manager_id | 951.8 KB | N/A |
| └─HashJoin_49 | 48381.05 | 24240 | root | | time:1m9s, loops:26, build_hash_table:{total:5.36ms, fetch:5.3ms, build:67.9µs}, probe:{concurrency:5, total:5m44.9s, max:1m9s, probe:15.3ms, fetch:5m44.9s} | inner join, equal:[eq(tpcds50.store_sales.ss_store_sk, tpcds50.store.s_store_sk)] | 8.23 KB | 0 Bytes |
| ├─TableReader_149(Build) | 144.00 | 144 | root | | time:5.26ms, loops:2, cop_task: {num: 1, max: 5.43ms, proc_keys: 0, rpc_num: 1, rpc_time: 5.42ms, copr_cache_hit_ratio: 0.00} | data:TableFullScan_148 | 1.30 KB | N/A |
| │ └─TableFullScan_148 | 144.00 | 144 | cop[tiflash] | table:store | tiflash_task:{time:4.4ms, loops:1, threads:1} | keep order:false | N/A | N/A |
| └─HashJoin_80(Probe) | 48381.05 | 24240 | root | | time:1m9s, loops:26, build_hash_table:{total:12.2ms, fetch:12.1ms, build:108.6µs}, probe:{concurrency:5, total:5m44.9s, max:1m9s, probe:520.5ms, fetch:5m44.4s} | inner join, equal:[eq(tpcds50.store_sales.ss_sold_date_sk, tpcds50.date_dim.d_date_sk)] | 24.7 KB | 0 Bytes |
| ├─TableReader_145(Build) | 364.00 | 365 | root | | time:12ms, loops:2, cop_task: {num: 1, max: 12.3ms, proc_keys: 0, rpc_num: 1, rpc_time: 12.3ms, copr_cache_hit_ratio: 0.00} | data:Selection_144 | 8.78 KB | N/A |
| │ └─Selection_144 | 364.00 | 365 | cop[tiflash] | | tiflash_task:{time:10.7ms, loops:1, threads:1} | in(tpcds50.date_dim.d_month_seq, 1212, 1213, 1214, 1215, 1216, 1217, 1218, 1219, 1220, 1221, 1222, 1223) | N/A | N/A |
| │ └─TableFullScan_143 | 73049.00 | 73049 | cop[tiflash] | table:date_dim | tiflash_task:{time:10.7ms, loops:2, threads:1} | keep order:false | N/A | N/A |
| └─IndexHashJoin_94(Probe) | 221084.16 | 5684892 | root | | time:1m9s, loops:5555, inner:{total:3m42.7s, concurrency:5, task:4, construct:2ms, fetch:3m39.7s, build:639.6µs, join:3.04s} | inner join, inner:IndexLookUp_91, outer key:tpcds50.item.i_item_sk, inner key:tpcds50.store_sales.ss_item_sk, equal cond:eq(tpcds50.item.i_item_sk, tpcds50.store_sales.ss_item_sk) | 372.9 MB | N/A |
| ├─TableReader_133(Build) | 95.80 | 3240 | root | | time:32.6ms, loops:6, cop_task: {num: 1, max: 32.7ms, proc_keys: 0, rpc_num: 1, rpc_time: 32.7ms, copr_cache_hit_ratio: 0.00} | data:Selection_132 | 219.6 KB | N/A |
| │ └─Selection_132 | 95.80 | 3240 | cop[tiflash] | | tiflash_task:{time:28.8ms, loops:1, threads:1} | or(and(in(tpcds50.item.i_category, "Books", "Children", "Electronics"), and(in(tpcds50.item.i_class, "personal", "portable", "reference", "self-help"), in(tpcds50.item.i_brand, "scholaramalgamalg #14", "scholaramalgamalg #7", "exportiunivamalg #9", "scholaramalgamalg #9"))), and(in(tpcds50.item.i_category, "Women", "Music", "Men"), and(in(tpcds50.item.i_class, "accessories", "classical", "fragrances", "pants"), in(tpcds50.item.i_brand, "amalgimporto #1", "edu packscholar #1", "exportiimporto #1", "importoamalg #1")))) | N/A | N/A |
| │ └─TableFullScan_131 | 62000.00 | 62000 | cop[tiflash] | table:item | tiflash_task:{time:15.8ms, loops:1, threads:1} | keep order:false | N/A | N/A |
| └─IndexLookUp_91(Probe) | 2307.77 | 5684892 | root | | time:3m39.7s, loops:5558, index_task: {total_time: 3m28.8s, fetch_handle: 474.2ms, build: 464.2µs, wait: 3m28.3s}, table_task: {total_time: 17m57.7s, num: 313, concurrency: 20} | | 12.6 MB | N/A |
| ├─IndexRangeScan_88(Build) | 2529.25 | 6096870 | cop[tikv] | table:store_sales, index:PRIMARY(ss_item_sk, ss_ticket_number) | time:15.4ms, loops:6037, cop_task: {num: 150, max: 1.63s, min: 396µs, avg: 241.6ms, p95: 658.9ms, max_proc_keys: 88340, p95_proc_keys: 55671, tot_proc: 14.9s, tot_wait: 21.2s, rpc_num: 150, rpc_time: 36.2s, copr_cache_hit_ratio: 0.61}, tikv_task:{proc max:497ms, min:12ms, p80:207ms, p95:286ms, iters:6631, tasks:150}, scan_detail: {total_process_keys: 2219356, total_keys: 2220531, rocksdb: {delete_skipped_count: 0, key_skipped_count: 2219356, block: {cache_hit_count: 1123, read_count: 2862, read_byte: 32.4 MB}}} | range: decided by [eq(tpcds50.store_sales.ss_item_sk, tpcds50.item.i_item_sk)], keep order:false | N/A | N/A |
| └─Selection_90(Probe) | 2307.77 | 5684892 | cop[tikv] | | time:17m52.6s, loops:5983, cop_task: {num: 85524, max: 1.05s, min: 248.3µs, avg: 183.7ms, p95: 357ms, max_proc_keys: 117, p95_proc_keys: 90, tot_proc: 54m36s, tot_wait: 3h26m25.5s, rpc_num: 85524, rpc_time: 4h21m49s, copr_cache_hit_ratio: 0.16}, tikv_task:{proc max:111ms, min:0s, p80:19ms, p95:37ms, iters:168217, tasks:85524}, scan_detail: {total_process_keys: 5097798, total_keys: 5097866, rocksdb: {delete_skipped_count: 0, key_skipped_count: 136, block: {cache_hit_count: 9774402, read_count: 382548, read_byte: 6.16 GB}}} | not(isnull(tpcds50.store_sales.ss_sold_date_sk)), not(isnull(tpcds50.store_sales.ss_store_sk)) | N/A | N/A |
| └─TableRowIDScan_89 | 2529.25 | 6096870 | cop[tikv] | table:store_sales | tikv_task:{proc max:111ms, min:0s, p80:19ms, p95:37ms, iters:168217, tasks:85524} | keep order:false | N/A | N/A |
+--------------------------------------------------+-----------+---------+--------------+----------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
21 rows in set (1 min 10.70 sec)
```
### 3. What did you see instead (Required)
This is the output of TiDB Dashboard.
```
explain analyze select * from (select i_manager_id ,sum(ss_sales_price) sum_sales ,avg(sum(ss_sales_price)) over (partition by i_manager_id) avg_monthly_sales from item ,store_sales ,date_dim ,store where ss_item_sk = i_item_sk and ss_sold_date_sk = d_date_sk and ss_store_sk = s_store_sk and d_month_seq in (1212,1212+1,1212+2,1212+3,1212+4,1212+5,1212+6,1212+7,1212+8,1212+9,1212+10,1212+11) and (( i_category in ('Books','Children','Electronics') and i_class in ('personal','portable','reference','self-help') and i_brand in ('scholaramalgamalg #14','scholaramalgamalg #7', 'exportiunivamalg #9','scholaramalgamalg #9')) or( i_category in ('Women','Music','Men') and i_class in ('accessories','classical','fragrances','pants') and i_brand in ('amalgimporto #1','edu packscholar #1','exportiimporto #1', 'importoamalg #1'))) group by i_manager_id, d_moy) tmp1 where case when avg_monthly_sales > 0 then abs (sum_sales - avg_monthly_sales) / avg_monthly_sales else null end > 0.1 order by i_manager_id ,avg_monthly_sales ,sum_sales limit 100;
```
```
id task estRows operator info actRows execution info memory disk
Projection_24 root 0.80 tpcds50.item.i_manager_id, Column#104, Column#106 100 time:1m9s, loops:2, Concurrency:OFF 9.44 KB N/A
└─TopN_27 root 0.80 tpcds50.item.i_manager_id, Column#106, Column#104, offset:0, count:100 100 time:1m9s, loops:2 10.2 KB N/A
└─Selection_32 root 0.80 gt(case(gt(Column#106, 0), div(abs(minus(Column#104, Column#106)), Column#106), NULL), 0.1) 266 time:1m9s, loops:3 28.1 KB N/A
└─Window_33 root 1 avg(Column#104)->Column#106 over(partition by tpcds50.item.i_manager_id) 276 time:1m9s, loops:3 N/A N/A
└─Sort_150 root 1 tpcds50.item.i_manager_id 276 time:1m9s, loops:2 16.1 KB 0 Bytes
└─HashAgg_34 root 1 group by:tpcds50.date_dim.d_moy, tpcds50.item.i_manager_id, funcs:sum(tpcds50.store_sales.ss_sales_price)->Column#104, funcs:firstrow(tpcds50.item.i_manager_id)->tpcds50.item.i_manager_id 276 time:1m9s, loops:6, partial_worker:{wall_time:1m8.98892105s, concurrency:5, task_num:25, tot_wait:5m44.93899803s, tot_exec:4.907913ms, tot_time:5m44.944109003s, max:1m8.988882745s, p95:1m8.988882745s}, final_worker:{wall_time:1m8.989111313s, concurrency:5, task_num:25, tot_wait:5m44.944067601s, tot_exec:1.199022ms, tot_time:5m44.945273426s, max:1m8.989081408s, p95:1m8.989081408s} 951.8 KB N/A
└─HashJoin_49 root 48381.05 inner join, equal:[eq(tpcds50.store_sales.ss_store_sk, tpcds50.store.s_store_sk)] 24240 time:1m9s, loops:26, build_hash_table:{total:5.36ms, fetch:5.3ms, build:67.9µs}, probe:{concurrency:5, total:5m44.9s, max:1m9s, probe:15.3ms, fetch:5m44.9s} 8.23 KB 0 Bytes
├─HashJoin_80 root 48381.05 inner join, equal:[eq(tpcds50.store_sales.ss_sold_date_sk, tpcds50.date_dim.d_date_sk)] 24240 time:1m9s, loops:26, build_hash_table:{total:12.2ms, fetch:12.1ms, build:108.6µs}, probe:{concurrency:5, total:5m44.9s, max:1m9s, probe:520.5ms, fetch:5m44.4s} 24.7 KB 0 Bytes
│ ├─IndexHashJoin_94 root 221084.16 inner join, inner:IndexLookUp_91, outer key:tpcds50.item.i_item_sk, inner key:tpcds50.store_sales.ss_item_sk, equal cond:eq(tpcds50.item.i_item_sk, tpcds50.store_sales.ss_item_sk) 5684892 time:1m9s, loops:5555, inner:{total:3m42.7s, concurrency:5, task:4, construct:2ms, fetch:3m39.7s, build:639.6µs, join:3.04s} 372.9 MB N/A
│ │ ├─TableReader_133 root 95.80 data:Selection_132 3240 time:32.6ms, loops:6, cop_task: {num: 1, max: 32.7ms, proc_keys: 0, rpc_num: 1, rpc_time: 32.7ms, copr_cache_hit_ratio: 0.00} 219.6 KB N/A
│ │ │ └─Selection_132 cop[tiflash] 95.80 or(and(in(tpcds50.item.i_category, "Books", "Children", "Electronics"), and(in(tpcds50.item.i_class, "personal", "portable", "reference", "self-help"), in(tpcds50.item.i_brand, "scholaramalgamalg #14", "scholaramalgamalg #7", "exportiunivamalg #9", "scholaramalgamalg #9"))), and(in(tpcds50.item.i_category, "Women", "Music", "Men"), and(in(tpcds50.item.i_class, "accessories", "classical", "fragrances", "pants"), in(tpcds50.item.i_brand, "amalgimporto #1", "edu packscholar #1", "exportiimporto #1", "importoamalg #1")))) 3240 tiflash_task:{time:28.8ms, loops:1, threads:1} N/A N/A
│ │ │ └─TableFullScan_131 cop[tiflash] 62000 table:item, keep order:false 62000 tiflash_task:{time:15.8ms, loops:1, threads:1} N/A N/A
│ │ └─IndexLookUp_91 root 2307.77 5684892 time:3m39.7s, loops:5558, index_task: {total_time: 3m28.8s, fetch_handle: 474.2ms, build: 464.2µs, wait: 3m28.3s}, table_task: {total_time: 17m57.7s, num: 313, concurrency: 20} 12.6 MB N/A
│ │ ├─IndexRangeScan_88 cop[tikv] 2529.25 table:store_sales, index:PRIMARY(ss_item_sk, ss_ticket_number), range: decided by [eq(tpcds50.store_sales.ss_item_sk, tpcds50.item.i_item_sk)], keep order:false 6096870 time:15.4ms, loops:6037, cop_task: {num: 150, max: 1.63s, min: 396µs, avg: 241.6ms, p95: 658.9ms, max_proc_keys: 88340, p95_proc_keys: 55671, tot_proc: 14.9s, tot_wait: 21.2s, rpc_num: 291, rpc_time: 1m12.3s, copr_cache_hit_ratio: 0.61}, tikv_task:{proc max:497ms, min:12ms, p80:207ms, p95:286ms, iters:6631, tasks:150}, scan_detail: {total_process_keys: 2219356, total_keys: 2220531, rocksdb: {delete_skipped_count: 0, key_skipped_count: 2219356, block: {cache_hit_count: 1123, read_count: 2862, read_byte: 32.4 MB}}} N/A N/A
│ │ └─Selection_90 cop[tikv] 2307.77 not(isnull(tpcds50.store_sales.ss_sold_date_sk)), not(isnull(tpcds50.store_sales.ss_store_sk)) 5684892 time:17m52.6s, loops:5983, cop_task: {num: 85524, max: 1.05s, min: 248.3µs, avg: 183.7ms, p95: 357ms, max_proc_keys: 117, p95_proc_keys: 90, tot_proc: 54m36s, tot_wait: 3h26m25.5s, rpc_num: 170781, rpc_time: 8h42m57.5s, copr_cache_hit_ratio: 0.16}, tikv_task:{proc max:111ms, min:0s, p80:19ms, p95:37ms, iters:168217, tasks:85524}, scan_detail: {total_process_keys: 5097798, total_keys: 5097866, rocksdb: {delete_skipped_count: 0, key_skipped_count: 136, block: {cache_hit_count: 9774402, read_count: 382548, read_byte: 6.16 GB}}} N/A N/A
│ │ └─TableRowIDScan_89 cop[tikv] 2529.25 table:store_sales, keep order:false 6096870 tikv_task:{proc max:111ms, min:0s, p80:19ms, p95:37ms, iters:168217, tasks:85524} N/A N/A
│ └─TableReader_145 root 364.00 data:Selection_144 365 time:12ms, loops:2, cop_task: {num: 1, max: 12.3ms, proc_keys: 0, rpc_num: 1, rpc_time: 12.3ms, copr_cache_hit_ratio: 0.00} 8.78 KB N/A
│ └─Selection_144 cop[tiflash] 364.00 in(tpcds50.date_dim.d_month_seq, 1212, 1213, 1214, 1215, 1216, 1217, 1218, 1219, 1220, 1221, 1222, 1223) 365 tiflash_task:{time:10.7ms, loops:1, threads:1} N/A N/A
│ └─TableFullScan_143 cop[tiflash] 73049 table:date_dim, keep order:false 73049 tiflash_task:{time:10.7ms, loops:2, threads:1} N/A N/A
└─TableReader_149 root 144 data:TableFullScan_148 144 time:5.26ms, loops:2, cop_task: {num: 1, max: 5.43ms, proc_keys: 0, rpc_num: 1, rpc_time: 5.42ms, copr_cache_hit_ratio: 0.00} 1.30 KB N/A
└─TableFullScan_148 cop[tiflash] 144 table:store, keep order:false 144 tiflash_task:{time:4.4ms, loops:1, threads:1} N/A N/A
```
### 4. What is your TiDB version? (Required)
```
Release Version: v5.3.0-alpha-1053-g6e70be97c
Edition: Community
Git Commit Hash: 6e70be97c2e5cc3b636ed9f55cfe7e73dd309138
Git Branch: master
UTC Build Time: 2021-10-09 13:56:08
GoVersion: go1.16.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
```