The result if not correct when open tidb_opt_enable_late_materialization

[lilinghai]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

ch run and check

-- exepct result is 0 , but the wrong result if not stable and not 0
SELECT
    count(*)
FROM
    (
        SELECT
            c.c_id,
            c.c_d_id,
            c.c_w_id,
            c.c_balance c1,
            (
                SELECT
                    sum(ol_amount)
                FROM
                    orders STRAIGHT_JOIN order_line
                WHERE
                    OL_W_ID = O_W_ID
                    AND OL_D_ID = O_D_ID
                    AND OL_O_ID = O_ID
                    AND OL_DELIVERY_D IS NOT NULL
                    AND O_W_ID = 9
                    AND O_D_ID = c.C_D_ID
                    AND O_C_ID = c.C_ID
            ) sm,
            (
                SELECT
                    sum(h_amount)
                from
                    history
                WHERE
                    H_C_W_ID = 9
                    AND H_C_D_ID = c.C_D_ID
                    AND H_C_ID = c.C_ID
            ) smh
        FROM
            customer c
        WHERE
            c.c_w_id = 9
    ) t
WHERE
    c1 <> sm - smh;

-- wrong result plan
explain analyze SELECT     count(*) FROM     (         SELECT             c.c_id,             c.c_d_id,             c.c_w_id,             c.c_balance c1,             (                 SELECT                     sum(ol_amount)                 FROM                     orders STRAIGHT_JOIN order_line                 WHERE                     OL_W_ID = O_W_ID                     AND OL_D_ID = O_D_ID                     AND OL_O_ID = O_ID                     AND OL_DELIVERY_D IS NOT NULL                     AND O_W_ID = 9                     AND O_D_ID = c.C_D_ID                     AND O_C_ID = c.C_ID             ) sm,             (                 SELECT                     sum(h_amount)                 from                     history                 WHERE                     H_C_W_ID = 9                     AND H_C_D_ID = c.C_D_ID                     AND H_C_ID = c.C_ID             ) smh         FROM             customer c         WHERE             c.c_w_id = 9     ) t WHERE     c1 <> sm - smh;
+----------------------------------------------------------+-------------------+---------+--------------+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| id                                                       | estRows           | actRows | task         | access object    | execution info                                                                                                                                                                                                                                                                                                                                                                                   | operator info                                                                                                                                                                                                                                                                                     | memory  | disk |
+----------------------------------------------------------+-------------------+---------+--------------+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| StreamAgg_35                                             | 1.00              | 1       | root         |                  | time:92.6ms, loops:2, RRU:0.000000, WRU:0.000000                                                                                                                                                                                                                                                                                                                                                 | funcs:count(1)->Column#83                                                                                                                                                                                                                                                                         | 38.4 KB | N/A  |
| └─TableReader_152                                        | 2.99              | 5       | root         |                  | time:92.6ms, loops:2, cop_task: {num: 8, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00}                                                                                                                                                                                                                                                                                         | MppVersion: 1, data:ExchangeSender_151                                                                                                                                                                                                                                                            | 1.57 KB | N/A  |
|   └─ExchangeSender_151                                   | 2.99              | 5       | mpp[tiflash] |                  | tiflash_task:{proc max:93.8ms, min:89.7ms, avg: 91.6ms, p80:93.8ms, p95:93.8ms, iters:32, tasks:4, threads:32}                                                                                                                                                                                                                                                                                   | ExchangeType: PassThrough                                                                                                                                                                                                                                                                         | N/A     | N/A  |
|     └─HashJoin_148                                       | 2.99              | 5       | mpp[tiflash] |                  | tiflash_task:{proc max:93.3ms, min:89.7ms, avg: 90.6ms, p80:93.3ms, p95:93.3ms, iters:32, tasks:4, threads:32}                                                                                                                                                                                                                                                                                   | inner join, equal:[eq(tpcc.history.h_c_d_id, tpcc.customer.c_d_id) eq(tpcc.history.h_c_id, tpcc.customer.c_id)], other cond:ne(tpcc.customer.c_balance, minus(Column#72, Column#82))                                                                                                              | N/A     | N/A  |
|       ├─ExchangeReceiver_55(Build)                       | 2.99              | 120000  | mpp[tiflash] |                  | tiflash_task:{proc max:17.3ms, min:9.66ms, avg: 13.6ms, p80:17.3ms, p95:17.3ms, iters:32, tasks:4, threads:32}                                                                                                                                                                                                                                                                                   |                                                                                                                                                                                                                                                                                                   | N/A     | N/A  |
|       │ └─ExchangeSender_54                              | 2.99              | 30000   | mpp[tiflash] |                  | tiflash_task:{proc max:13.5ms, min:0s, avg: 3.39ms, p80:13.5ms, p95:13.5ms, iters:32, tasks:4, threads:32}                                                                                                                                                                                                                                                                                       | ExchangeType: Broadcast, Compression: FAST                                                                                                                                                                                                                                                        | N/A     | N/A  |
|       │   └─Projection_50                                | 2.99              | 30000   | mpp[tiflash] |                  | tiflash_task:{proc max:13.5ms, min:0s, avg: 3.39ms, p80:13.5ms, p95:13.5ms, iters:32, tasks:4, threads:32}                                                                                                                                                                                                                                                                                       | Column#82, tpcc.history.h_c_d_id, tpcc.history.h_c_id                                                                                                                                                                                                                                             | N/A     | N/A  |
|       │     └─HashAgg_51                                 | 2.99              | 30000   | mpp[tiflash] |                  | tiflash_task:{proc max:13.5ms, min:0s, avg: 3.39ms, p80:13.5ms, p95:13.5ms, iters:32, tasks:4, threads:32}                                                                                                                                                                                                                                                                                       | group by:tpcc.history.h_c_d_id, tpcc.history.h_c_id, funcs:sum(Column#84)->Column#82, funcs:firstrow(tpcc.history.h_c_d_id)->tpcc.history.h_c_d_id, funcs:firstrow(tpcc.history.h_c_id)->tpcc.history.h_c_id, stream_count: 8                                                                     | N/A     | N/A  |
|       │       └─ExchangeReceiver_53                      | 2.99              | 30000   | mpp[tiflash] |                  | tiflash_task:{proc max:13.5ms, min:0s, avg: 3.39ms, p80:13.5ms, p95:13.5ms, iters:32, tasks:4, threads:32}                                                                                                                                                                                                                                                                                       | stream_count: 8                                                                                                                                                                                                                                                                                   | N/A     | N/A  |
|       │         └─ExchangeSender_52                      | 2.99              | 30000   | mpp[tiflash] |                  | tiflash_task:{proc max:15.2ms, min:0s, avg: 3.81ms, p80:15.2ms, p95:15.2ms, iters:1, tasks:4, threads:32}                                                                                                                                                                                                                                                                                        | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: tpcc.history.h_c_d_id, collate: binary], [name: tpcc.history.h_c_id, collate: binary], stream_count: 8                                                                                                                          | N/A     | N/A  |
|       │           └─HashAgg_40                           | 2.99              | 30000   | mpp[tiflash] |                  | tiflash_task:{proc max:11.2ms, min:0s, avg: 2.81ms, p80:11.2ms, p95:11.2ms, iters:1, tasks:4, threads:32}                                                                                                                                                                                                                                                                                        | group by:tpcc.history.h_c_d_id, tpcc.history.h_c_id, funcs:sum(tpcc.history.h_amount)->Column#84                                                                                                                                                                                                  | N/A     | N/A  |
|       │             └─TableFullScan_48                   | 29908.33          | 30023   | mpp[tiflash] | table:history    | tiflash_task:{proc max:7.24ms, min:0s, avg: 1.81ms, p80:7.24ms, p95:7.24ms, iters:10, tasks:4, threads:32}, tiflash_scan:{dtfile:{total_scanned_packs:53, total_skipped_packs:3931, total_scanned_rows:434176, total_skipped_rows:31963836, total_rs_index_load_time: 0ms, total_read_time: 0ms}, total_create_snapshot_time: 0ms, total_local_region_num: 0, total_remote_region_num: 0}        | pushed down filter:eq(tpcc.history.h_c_w_id, 9), keep order:false                                                                                                                                                                                                                                 | N/A     | N/A  |
|       └─Projection_76(Probe)                             | 29883.56          | 27007   | mpp[tiflash] |                  | tiflash_task:{proc max:81.7ms, min:77.3ms, avg: 78.6ms, p80:81.7ms, p95:81.7ms, iters:32, tasks:4, threads:32}                                                                                                                                                                                                                                                                                   | tpcc.customer.c_id, tpcc.customer.c_d_id, tpcc.customer.c_balance, Column#72                                                                                                                                                                                                                      | N/A     | N/A  |
|         └─HashAgg_77                                     | 29883.56          | 27007   | mpp[tiflash] |                  | tiflash_task:{proc max:81.7ms, min:77.3ms, avg: 78.6ms, p80:81.7ms, p95:81.7ms, iters:32, tasks:4, threads:32}                                                                                                                                                                                                                                                                                   | group by:tpcc.customer.c_d_id, tpcc.customer.c_id, tpcc.customer.c_w_id, funcs:firstrow(tpcc.customer.c_id)->tpcc.customer.c_id, funcs:firstrow(tpcc.customer.c_d_id)->tpcc.customer.c_d_id, funcs:firstrow(Column#89)->tpcc.customer.c_balance, funcs:sum(Column#90)->Column#72, stream_count: 8 | N/A     | N/A  |
|           └─ExchangeReceiver_79                          | 29883.56          | 27007   | mpp[tiflash] |                  | tiflash_task:{proc max:81.7ms, min:77.3ms, avg: 78.6ms, p80:81.7ms, p95:81.7ms, iters:32, tasks:4, threads:32}                                                                                                                                                                                                                                                                                   | stream_count: 8                                                                                                                                                                                                                                                                                   | N/A     | N/A  |
|             └─ExchangeSender_78                          | 29883.56          | 27007   | mpp[tiflash] |                  | tiflash_task:{proc max:90.2ms, min:0s, avg: 22.5ms, p80:90.2ms, p95:90.2ms, iters:4, tasks:4, threads:32}                                                                                                                                                                                                                                                                                        | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: tpcc.customer.c_w_id, collate: binary], [name: tpcc.customer.c_d_id, collate: binary], [name: tpcc.customer.c_id, collate: binary], stream_count: 8                                                                             | N/A     | N/A  |
|               └─HashAgg_57                               | 29883.56          | 27007   | mpp[tiflash] |                  | tiflash_task:{proc max:90.2ms, min:0s, avg: 22.5ms, p80:90.2ms, p95:90.2ms, iters:4, tasks:4, threads:32}                                                                                                                                                                                                                                                                                        | group by:tpcc.customer.c_d_id, tpcc.customer.c_id, tpcc.customer.c_w_id, funcs:firstrow(tpcc.customer.c_balance)->Column#89, funcs:sum(tpcc.order_line.ol_amount)->Column#90                                                                                                                      | N/A     | N/A  |
|                 └─HashJoin_75                            | 20851528165396.64 | 218516  | mpp[tiflash] |                  | tiflash_task:{proc max:90.2ms, min:0s, avg: 22.5ms, p80:90.2ms, p95:90.2ms, iters:67, tasks:4, threads:32}                                                                                                                                                                                                                                                                                       | left outer join, equal:[eq(tpcc.customer.c_d_id, tpcc.orders.o_d_id) eq(tpcc.customer.c_id, tpcc.orders.o_c_id)], stream_count: 8                                                                                                                                                                 | N/A     | N/A  |
|                   ├─ExchangeReceiver_62(Build)           | 29883.56          | 30000   | mpp[tiflash] |                  | tiflash_task:{proc max:38.2ms, min:0s, avg: 9.54ms, p80:38.2ms, p95:38.2ms, iters:32, tasks:4, threads:32}                                                                                                                                                                                                                                                                                       | stream_count: 8                                                                                                                                                                                                                                                                                   | N/A     | N/A  |
|                   │ └─ExchangeSender_61                  | 29883.56          | 30000   | mpp[tiflash] |                  | tiflash_task:{proc max:37.6ms, min:0s, avg: 9.4ms, p80:37.6ms, p95:37.6ms, iters:13, tasks:4, threads:32}                                                                                                                                                                                                                                                                                        | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: tpcc.customer.c_d_id, collate: binary], [name: tpcc.customer.c_id, collate: binary], stream_count: 8                                                                                                                            | N/A     | N/A  |
|                   │   └─TableFullScan_59                 | 29883.56          | 30000   | mpp[tiflash] | table:c          | tiflash_task:{proc max:37.6ms, min:0s, avg: 9.4ms, p80:37.6ms, p95:37.6ms, iters:13, tasks:4, threads:32}, tiflash_scan:{dtfile:{total_scanned_packs:109, total_skipped_packs:21230, total_scanned_rows:884084, total_skipped_rows:171281140, total_rs_index_load_time: 0ms, total_read_time: 10ms}, total_create_snapshot_time: 0ms, total_local_region_num: 0, total_remote_region_num: 0}     | pushed down filter:eq(tpcc.customer.c_w_id, 9), keep order:false                                                                                                                                                                                                                                  | N/A     | N/A  |
|                   └─ExchangeReceiver_71(Probe)           | 2086026374.63     | 210151  | mpp[tiflash] |                  | tiflash_task:{proc max:50.2ms, min:0s, avg: 12.5ms, p80:50.2ms, p95:50.2ms, iters:35, tasks:4, threads:32}                                                                                                                                                                                                                                                                                       |                                                                                                                                                                                                                                                                                                   | N/A     | N/A  |
|                     └─ExchangeSender_70                  | 2086026374.63     | 210151  | mpp[tiflash] |                  | tiflash_task:{proc max:82.7ms, min:0s, avg: 20.7ms, p80:82.7ms, p95:82.7ms, iters:25, tasks:4, threads:32}                                                                                                                                                                                                                                                                                       | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: tpcc.orders.o_d_id, collate: binary], [name: tpcc.orders.o_c_id, collate: binary]                                                                                                                                               | N/A     | N/A  |
|                       └─HashJoin_63                      | 2086026374.63     | 210151  | mpp[tiflash] |                  | tiflash_task:{proc max:82.7ms, min:0s, avg: 20.7ms, p80:82.7ms, p95:82.7ms, iters:25, tasks:4, threads:32}                                                                                                                                                                                                                                                                                       | inner join, equal:[eq(tpcc.orders.o_d_id, tpcc.order_line.ol_d_id) eq(tpcc.orders.o_id, tpcc.order_line.ol_o_id)]                                                                                                                                                                                 | N/A     | N/A  |
|                         ├─ExchangeReceiver_67(Build)     | 29896.08          | 120104  | mpp[tiflash] |                  | tiflash_task:{proc max:26.7ms, min:0s, avg: 6.67ms, p80:26.7ms, p95:26.7ms, iters:26, tasks:4, threads:32}                                                                                                                                                                                                                                                                                       |                                                                                                                                                                                                                                                                                                   | N/A     | N/A  |
|                         │ └─ExchangeSender_66            | 29896.08          | 30026   | mpp[tiflash] |                  | tiflash_task:{proc max:26.9ms, min:0s, avg: 6.73ms, p80:26.9ms, p95:26.9ms, iters:12, tasks:4, threads:17}                                                                                                                                                                                                                                                                                       | ExchangeType: Broadcast, Compression: FAST                                                                                                                                                                                                                                                        | N/A     | N/A  |
|                         │   └─Selection_65               | 29896.08          | 30026   | mpp[tiflash] |                  | tiflash_task:{proc max:22.9ms, min:0s, avg: 5.73ms, p80:22.9ms, p95:22.9ms, iters:12, tasks:4, threads:17}                                                                                                                                                                                                                                                                                       | not(isnull(tpcc.orders.o_c_id))                                                                                                                                                                                                                                                                   | N/A     | N/A  |
|                         │     └─TableFullScan_64         | 29896.08          | 30026   | mpp[tiflash] | table:orders     | tiflash_task:{proc max:22.9ms, min:0s, avg: 5.73ms, p80:22.9ms, p95:22.9ms, iters:12, tasks:4, threads:17}, tiflash_scan:{dtfile:{total_scanned_packs:95, total_skipped_packs:11605, total_scanned_rows:778240, total_skipped_rows:94387601, total_rs_index_load_time: 0ms, total_read_time: 9ms}, total_create_snapshot_time: 0ms, total_local_region_num: 0, total_remote_region_num: 0}       | pushed down filter:eq(tpcc.orders.o_w_id, 9), keep order:false                                                                                                                                                                                                                                    | N/A     | N/A  |
|                         └─Selection_69(Probe)            | 208602.64         | 210151  | mpp[tiflash] |                  | tiflash_task:{proc max:70.7ms, min:0s, avg: 17.7ms, p80:70.7ms, p95:70.7ms, iters:25, tasks:4, threads:32}                                                                                                                                                                                                                                                                                       | not(isnull(tpcc.order_line.ol_delivery_d))                                                                                                                                                                                                                                                        | N/A     | N/A  |
|                           └─TableFullScan_68             | 298017.30         | 300072  | mpp[tiflash] | table:order_line | tiflash_task:{proc max:70.7ms, min:0s, avg: 17.7ms, p80:70.7ms, p95:70.7ms, iters:37, tasks:4, threads:32}, tiflash_scan:{dtfile:{total_scanned_packs:1051, total_skipped_packs:109862, total_scanned_rows:8586484, total_skipped_rows:895310498, total_rs_index_load_time: 4ms, total_read_time: 88ms}, total_create_snapshot_time: 0ms, total_local_region_num: 0, total_remote_region_num: 0} | pushed down filter:eq(tpcc.order_line.ol_w_id, 9), keep order:false                                                                                                                                                                                                                               | N/A     | N/A  |
+----------------------------------------------------------+-------------------+---------+--------------+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+

-- correct result plan
explain analyze SELECT     count(*) FROM     (         SELECT             c.c_id,             c.c_d_id,             c.c_w_id,             c.c_balance c1,             (                 SELECT                     sum(ol_amount)                 FROM                     orders STRAIGHT_JOIN order_line                 WHERE                     OL_W_ID = O_W_ID                     AND OL_D_ID = O_D_ID                     AND OL_O_ID = O_ID                     AND OL_DELIVERY_D IS NOT NULL                     AND O_W_ID = 9                     AND O_D_ID = c.C_D_ID                     AND O_C_ID = c.C_ID             ) sm,             (                 SELECT                     sum(h_amount)                 from                     history                 WHERE                     H_C_W_ID = 9                     AND H_C_D_ID = c.C_D_ID                     AND H_C_ID = c.C_ID             ) smh         FROM             customer c         WHERE             c.c_w_id = 9     ) t WHERE     c1 <> sm - smh;
+----------------------------------------------------------+-------------------+---------+--------------+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| id                                                       | estRows           | actRows | task         | access object    | execution info                                                                                                                                                                                                                                                                                                                                                                                    | operator info                                                                                                                                                                                                                                                                                     | memory  | disk |
+----------------------------------------------------------+-------------------+---------+--------------+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| StreamAgg_35                                             | 1.00              | 1       | root         |                  | time:99.2ms, loops:2, RRU:0.000000, WRU:0.000000                                                                                                                                                                                                                                                                                                                                                  | funcs:count(1)->Column#83                                                                                                                                                                                                                                                                         | 38.4 KB | N/A  |
| └─TableReader_152                                        | 2.99              | 0       | root         |                  | time:99.2ms, loops:1, cop_task: {num: 4, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00}                                                                                                                                                                                                                                                                                          | MppVersion: 1, data:ExchangeSender_151                                                                                                                                                                                                                                                            | 2.52 KB | N/A  |
|   └─ExchangeSender_151                                   | 2.99              | 0       | mpp[tiflash] |                  | tiflash_task:{proc max:98.1ms, min:97.5ms, avg: 97.8ms, p80:98.1ms, p95:98.1ms, iters:32, tasks:4, threads:32}                                                                                                                                                                                                                                                                                    | ExchangeType: PassThrough                                                                                                                                                                                                                                                                         | N/A     | N/A  |
|     └─HashJoin_148                                       | 2.99              | 0       | mpp[tiflash] |                  | tiflash_task:{proc max:98.1ms, min:93.5ms, avg: 96.8ms, p80:98.1ms, p95:98.1ms, iters:32, tasks:4, threads:32}                                                                                                                                                                                                                                                                                    | inner join, equal:[eq(tpcc.history.h_c_d_id, tpcc.customer.c_d_id) eq(tpcc.history.h_c_id, tpcc.customer.c_id)], other cond:ne(tpcc.customer.c_balance, minus(Column#72, Column#82))                                                                                                              | N/A     | N/A  |
|       ├─ExchangeReceiver_55(Build)                       | 2.99              | 120000  | mpp[tiflash] |                  | tiflash_task:{proc max:18.1ms, min:17.5ms, avg: 17.8ms, p80:18.1ms, p95:18.1ms, iters:32, tasks:4, threads:32}                                                                                                                                                                                                                                                                                    |                                                                                                                                                                                                                                                                                                   | N/A     | N/A  |
|       │ └─ExchangeSender_54                              | 2.99              | 30000   | mpp[tiflash] |                  | tiflash_task:{proc max:18ms, min:0s, avg: 4.5ms, p80:18ms, p95:18ms, iters:32, tasks:4, threads:32}                                                                                                                                                                                                                                                                                               | ExchangeType: Broadcast, Compression: FAST                                                                                                                                                                                                                                                        | N/A     | N/A  |
|       │   └─Projection_50                                | 2.99              | 30000   | mpp[tiflash] |                  | tiflash_task:{proc max:18ms, min:0s, avg: 4.5ms, p80:18ms, p95:18ms, iters:32, tasks:4, threads:32}                                                                                                                                                                                                                                                                                               | Column#82, tpcc.history.h_c_d_id, tpcc.history.h_c_id                                                                                                                                                                                                                                             | N/A     | N/A  |
|       │     └─HashAgg_51                                 | 2.99              | 30000   | mpp[tiflash] |                  | tiflash_task:{proc max:18ms, min:0s, avg: 4.5ms, p80:18ms, p95:18ms, iters:32, tasks:4, threads:32}                                                                                                                                                                                                                                                                                               | group by:tpcc.history.h_c_d_id, tpcc.history.h_c_id, funcs:sum(Column#84)->Column#82, funcs:firstrow(tpcc.history.h_c_d_id)->tpcc.history.h_c_d_id, funcs:firstrow(tpcc.history.h_c_id)->tpcc.history.h_c_id, stream_count: 8                                                                     | N/A     | N/A  |
|       │       └─ExchangeReceiver_53                      | 2.99              | 30023   | mpp[tiflash] |                  | tiflash_task:{proc max:18ms, min:0s, avg: 4.5ms, p80:18ms, p95:18ms, iters:32, tasks:4, threads:32}                                                                                                                                                                                                                                                                                               | stream_count: 8                                                                                                                                                                                                                                                                                   | N/A     | N/A  |
|       │         └─ExchangeSender_52                      | 2.99              | 30023   | mpp[tiflash] |                  | tiflash_task:{proc max:18.4ms, min:0s, avg: 4.6ms, p80:18.4ms, p95:18.4ms, iters:2, tasks:4, threads:32}                                                                                                                                                                                                                                                                                          | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: tpcc.history.h_c_d_id, collate: binary], [name: tpcc.history.h_c_id, collate: binary], stream_count: 8                                                                                                                          | N/A     | N/A  |
|       │           └─HashAgg_40                           | 2.99              | 30023   | mpp[tiflash] |                  | tiflash_task:{proc max:18.4ms, min:0s, avg: 4.6ms, p80:18.4ms, p95:18.4ms, iters:2, tasks:4, threads:32}                                                                                                                                                                                                                                                                                          | group by:tpcc.history.h_c_d_id, tpcc.history.h_c_id, funcs:sum(tpcc.history.h_amount)->Column#84                                                                                                                                                                                                  | N/A     | N/A  |
|       │             └─Selection_49                       | 29908.33          | 30023   | mpp[tiflash] |                  | tiflash_task:{proc max:18.4ms, min:0s, avg: 4.6ms, p80:18.4ms, p95:18.4ms, iters:11, tasks:4, threads:32}                                                                                                                                                                                                                                                                                         | eq(tpcc.history.h_c_w_id, 9)                                                                                                                                                                                                                                                                      | N/A     | N/A  |
|       │               └─TableFullScan_48                 | 30022664.00       | 366728  | mpp[tiflash] | table:history    | tiflash_task:{proc max:18.4ms, min:0s, avg: 4.6ms, p80:18.4ms, p95:18.4ms, iters:20, tasks:4, threads:32}, tiflash_scan:{dtfile:{total_scanned_packs:42, total_skipped_packs:3767, total_scanned_rows:344064, total_skipped_rows:30621470, total_rs_index_load_time: 0ms, total_read_time: 2ms}, total_create_snapshot_time: 0ms, total_local_region_num: 0, total_remote_region_num: 0}          | keep order:false                                                                                                                                                                                                                                                                                  | N/A     | N/A  |
|       └─Projection_76(Probe)                             | 29883.56          | 30000   | mpp[tiflash] |                  | tiflash_task:{proc max:81.8ms, min:77.5ms, avg: 78.8ms, p80:81.8ms, p95:81.8ms, iters:32, tasks:4, threads:32}                                                                                                                                                                                                                                                                                    | tpcc.customer.c_id, tpcc.customer.c_d_id, tpcc.customer.c_balance, Column#72                                                                                                                                                                                                                      | N/A     | N/A  |
|         └─HashAgg_77                                     | 29883.56          | 30000   | mpp[tiflash] |                  | tiflash_task:{proc max:81.8ms, min:77.5ms, avg: 78.8ms, p80:81.8ms, p95:81.8ms, iters:32, tasks:4, threads:32}                                                                                                                                                                                                                                                                                    | group by:tpcc.customer.c_d_id, tpcc.customer.c_id, tpcc.customer.c_w_id, funcs:firstrow(tpcc.customer.c_id)->tpcc.customer.c_id, funcs:firstrow(tpcc.customer.c_d_id)->tpcc.customer.c_d_id, funcs:firstrow(Column#89)->tpcc.customer.c_balance, funcs:sum(Column#90)->Column#72, stream_count: 8 | N/A     | N/A  |
|           └─ExchangeReceiver_79                          | 29883.56          | 30000   | mpp[tiflash] |                  | tiflash_task:{proc max:81.8ms, min:77.5ms, avg: 78.8ms, p80:81.8ms, p95:81.8ms, iters:32, tasks:4, threads:32}                                                                                                                                                                                                                                                                                    | stream_count: 8                                                                                                                                                                                                                                                                                   | N/A     | N/A  |
|             └─ExchangeSender_78                          | 29883.56          | 30000   | mpp[tiflash] |                  | tiflash_task:{proc max:97.9ms, min:0s, avg: 24.5ms, p80:97.9ms, p95:97.9ms, iters:4, tasks:4, threads:32}                                                                                                                                                                                                                                                                                         | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: tpcc.customer.c_w_id, collate: binary], [name: tpcc.customer.c_d_id, collate: binary], [name: tpcc.customer.c_id, collate: binary], stream_count: 8                                                                             | N/A     | N/A  |
|               └─HashAgg_57                               | 29883.56          | 30000   | mpp[tiflash] |                  | tiflash_task:{proc max:94.3ms, min:0s, avg: 23.6ms, p80:94.3ms, p95:94.3ms, iters:4, tasks:4, threads:32}                                                                                                                                                                                                                                                                                         | group by:tpcc.customer.c_d_id, tpcc.customer.c_id, tpcc.customer.c_w_id, funcs:firstrow(tpcc.customer.c_balance)->Column#89, funcs:sum(tpcc.order_line.ol_amount)->Column#90                                                                                                                      | N/A     | N/A  |
|                 └─HashJoin_75                            | 20851528165396.64 | 219121  | mpp[tiflash] |                  | tiflash_task:{proc max:94.3ms, min:0s, avg: 23.6ms, p80:94.3ms, p95:94.3ms, iters:65, tasks:4, threads:32}                                                                                                                                                                                                                                                                                        | left outer join, equal:[eq(tpcc.customer.c_d_id, tpcc.orders.o_d_id) eq(tpcc.customer.c_id, tpcc.orders.o_c_id)], stream_count: 8                                                                                                                                                                 | N/A     | N/A  |
|                   ├─ExchangeReceiver_62(Build)           | 29883.56          | 30000   | mpp[tiflash] |                  | tiflash_task:{proc max:34ms, min:0s, avg: 8.49ms, p80:34ms, p95:34ms, iters:32, tasks:4, threads:32}                                                                                                                                                                                                                                                                                              | stream_count: 8                                                                                                                                                                                                                                                                                   | N/A     | N/A  |
|                   │ └─ExchangeSender_61                  | 29883.56          | 30000   | mpp[tiflash] |                  | tiflash_task:{proc max:29.7ms, min:0s, avg: 7.41ms, p80:29.7ms, p95:29.7ms, iters:13, tasks:4, threads:32}                                                                                                                                                                                                                                                                                        | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: tpcc.customer.c_d_id, collate: binary], [name: tpcc.customer.c_id, collate: binary], stream_count: 8                                                                                                                            | N/A     | N/A  |
|                   │   └─Selection_60                     | 29883.56          | 30000   | mpp[tiflash] |                  | tiflash_task:{proc max:29.7ms, min:0s, avg: 7.41ms, p80:29.7ms, p95:29.7ms, iters:13, tasks:4, threads:32}                                                                                                                                                                                                                                                                                        | eq(tpcc.customer.c_w_id, 9)                                                                                                                                                                                                                                                                       | N/A     | N/A  |
|                   │     └─TableFullScan_59               | 30000000.00       | 414978  | mpp[tiflash] | table:c          | tiflash_task:{proc max:29.7ms, min:0s, avg: 7.41ms, p80:29.7ms, p95:29.7ms, iters:585, tasks:4, threads:32}, tiflash_scan:{dtfile:{total_scanned_packs:92, total_skipped_packs:14268, total_scanned_rows:744820, total_skipped_rows:115116348, total_rs_index_load_time: 0ms, total_read_time: 11ms}, total_create_snapshot_time: 0ms, total_local_region_num: 0, total_remote_region_num: 0}     | keep order:false                                                                                                                                                                                                                                                                                  | N/A     | N/A  |
|                   └─ExchangeReceiver_71(Probe)           | 2086026374.63     | 210151  | mpp[tiflash] |                  | tiflash_task:{proc max:65.9ms, min:0s, avg: 16.5ms, p80:65.9ms, p95:65.9ms, iters:33, tasks:4, threads:32}                                                                                                                                                                                                                                                                                        |                                                                                                                                                                                                                                                                                                   | N/A     | N/A  |
|                     └─ExchangeSender_70                  | 2086026374.63     | 210151  | mpp[tiflash] |                  | tiflash_task:{proc max:89.9ms, min:0s, avg: 22.5ms, p80:89.9ms, p95:89.9ms, iters:25, tasks:4, threads:32}                                                                                                                                                                                                                                                                                        | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: tpcc.orders.o_d_id, collate: binary], [name: tpcc.orders.o_c_id, collate: binary]                                                                                                                                               | N/A     | N/A  |
|                       └─HashJoin_63                      | 2086026374.63     | 210151  | mpp[tiflash] |                  | tiflash_task:{proc max:89.9ms, min:0s, avg: 22.5ms, p80:89.9ms, p95:89.9ms, iters:25, tasks:4, threads:32}                                                                                                                                                                                                                                                                                        | inner join, equal:[eq(tpcc.orders.o_d_id, tpcc.order_line.ol_d_id) eq(tpcc.orders.o_id, tpcc.order_line.ol_o_id)]                                                                                                                                                                                 | N/A     | N/A  |
|                         ├─ExchangeReceiver_67(Build)     | 29896.08          | 120104  | mpp[tiflash] |                  | tiflash_task:{proc max:29.9ms, min:0s, avg: 7.48ms, p80:29.9ms, p95:29.9ms, iters:27, tasks:4, threads:32}                                                                                                                                                                                                                                                                                        |                                                                                                                                                                                                                                                                                                   | N/A     | N/A  |
|                         │ └─ExchangeSender_66            | 29896.08          | 30026   | mpp[tiflash] |                  | tiflash_task:{proc max:31.5ms, min:0s, avg: 7.87ms, p80:31.5ms, p95:31.5ms, iters:13, tasks:4, threads:17}                                                                                                                                                                                                                                                                                        | ExchangeType: Broadcast, Compression: FAST                                                                                                                                                                                                                                                        | N/A     | N/A  |
|                         │   └─Selection_65               | 29896.08          | 30026   | mpp[tiflash] |                  | tiflash_task:{proc max:31.5ms, min:0s, avg: 7.87ms, p80:31.5ms, p95:31.5ms, iters:13, tasks:4, threads:17}                                                                                                                                                                                                                                                                                        | eq(tpcc.orders.o_w_id, 9), not(isnull(tpcc.orders.o_c_id))                                                                                                                                                                                                                                        | N/A     | N/A  |
|                         │     └─TableFullScan_64         | 30023294.00       | 387645  | mpp[tiflash] | table:orders     | tiflash_task:{proc max:27.5ms, min:0s, avg: 6.87ms, p80:27.5ms, p95:27.5ms, iters:111, tasks:4, threads:17}, tiflash_scan:{dtfile:{total_scanned_packs:84, total_skipped_packs:7490, total_scanned_rows:688128, total_skipped_rows:60930998, total_rs_index_load_time: 0ms, total_read_time: 9ms}, total_create_snapshot_time: 0ms, total_local_region_num: 0, total_remote_region_num: 0}        | keep order:false                                                                                                                                                                                                                                                                                  | N/A     | N/A  |
|                         └─Selection_69(Probe)            | 208602.64         | 210151  | mpp[tiflash] |                  | tiflash_task:{proc max:73.9ms, min:0s, avg: 18.5ms, p80:73.9ms, p95:73.9ms, iters:25, tasks:4, threads:32}                                                                                                                                                                                                                                                                                        | eq(tpcc.order_line.ol_w_id, 9), not(isnull(tpcc.order_line.ol_delivery_d))                                                                                                                                                                                                                        | N/A     | N/A  |
|                           └─TableFullScan_68             | 300212224.00      | 3964422 | mpp[tiflash] | table:order_line | tiflash_task:{proc max:73.9ms, min:0s, avg: 18.5ms, p80:73.9ms, p95:73.9ms, iters:1152, tasks:4, threads:32}, tiflash_scan:{dtfile:{total_scanned_packs:904, total_skipped_packs:73038, total_scanned_rows:7382798, total_skipped_rows:595215190, total_rs_index_load_time: 0ms, total_read_time: 106ms}, total_create_snapshot_time: 0ms, total_local_region_num: 0, total_remote_region_num: 0} | keep order:false                                                                                                                                                                                                                                                                                  | N/A     | N/A  |
+----------------------------------------------------------+-------------------+---------+--------------+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+

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

3. What did you see instead (Required)

4. What is your TiDB version? (Required)

Release Version: v7.1.0-alpha
Edition: Community
Git Commit Hash: f5ca27e
Git Branch: heads/refs/tags/v7.1.0-alpha
UTC Build Time: 2023-03-23 11:41:43
GoVersion: go1.20.2
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv

[Lloyd-Pottiger]

/assign