FastScan may return more data than expected

[JinheLin]

| id                                     | estRows       | estCost          | actRows    | task         | access object | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         | operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   | memory  | disk  |
| ImportInto_2                           | 0.00          | 0.00             | 0          | root         |               | time:1h30m32.7s, loops:1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               | N/A                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             | N/A     | N/A   |
| └─TableReader_61                       | 173360660.79  | 62.32            | 1135116119 | root         |               | time:52.9s, loops:1136170, cop_task: {num: 66355, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | MppVersion: 2, data:ExchangeSender_60                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           | 60.0 MB | N/A   |
|   └─ExchangeSender_60                  | 173360660.79  | 2303821113274.59 | 1135116119 | mpp[tiflash] |               | tiflash_task:{time:1h18m6.9s, loops:66354, threads:64}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | ExchangeType: PassThrough                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       | N/A     | N/A   |
|     └─Projection_12                    | 173360660.79  | 2303821113274.59 | 1135116119 | mpp[tiflash] |               | tiflash_task:{time:3m35.2s, loops:66354, threads:64}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   | zh.day_bill_dy_yf.plan_no, zh.all_plan_tmp_test123.loan_no, zh.all_plan_tmp_test123.term_no, zh.all_plan_tmp_test123.start_date, zh.all_plan_tmp_test123.end_date, zh.all_plan_tmp_test123.clear_date, zh.all_plan_tmp_test123.grace_date, zh.all_plan_tmp_test123.grace_end_date, zh.all_plan_tmp_test123.billing_date, zh.all_plan_tmp_test123.status, zh.all_plan_tmp_test123.prin_amt, zh.all_plan_tmp_test123.due_intr_amt, zh.all_plan_tmp_test123.due_fee_amt, zh.all_plan_tmp_test123.prin_bal, zh.all_plan_tmp_test123.intr_bal, zh.all_plan_tmp_test123.prin_penal_bal, zh.all_plan_tmp_test123.intr_penal_bal, zh.all_plan_tmp_test123.fee_bal, zh.all_plan_tmp_test123.fee_penal_bal, zh.all_plan_tmp_test123.other_fee_bal, zh.all_plan_tmp_test123.prin_paid, zh.all_plan_tmp_test123.intr_paid, zh.all_plan_tmp_test123.prin_penal_paid, zh.all_plan_tmp_test123.intr_penal_paid, zh.all_plan_tmp_test123.fee_paid, zh.all_plan_tmp_test123.fee_penal_paid, zh.all_plan_tmp_test123.other_fee_paid, zh.all_plan_tmp_test123.prin_fund_amt, zh.all_plan_tmp_test123.intr_fund_amt, zh.all_plan_tmp_test123.prin_penal_fund_amt, zh.all_plan_tmp_test123.intr_penal_fund_amt, zh.all_plan_tmp_test123.fee_fund_amt, zh.all_plan_tmp_test123.fee_penal_fund_amt, zh.all_plan_tmp_test123.other_fee_fund_amt, zh.all_plan_tmp_test123.rmt_intr_amt, zh.all_plan_tmp_test123.prin_penal_rmt, zh.all_plan_tmp_test123.intr_penal_rmt, zh.all_plan_tmp_test123.fee_rmt, zh.all_plan_tmp_test123.fee_penal_rmt, zh.all_plan_tmp_test123.other_fee_rmt, zh.all_plan_tmp_test123.data_date | N/A     | N/A   |
|       └─Projection_59                  | 173360660.79  | 2303816848602.34 | 1135116119 | mpp[tiflash] |               | tiflash_task:{time:3m35.2s, loops:66354, threads:64}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   | zh.all_plan_tmp_test123.loan_no, zh.all_plan_tmp_test123.term_no, zh.all_plan_tmp_test123.start_date, zh.all_plan_tmp_test123.end_date, zh.all_plan_tmp_test123.clear_date, zh.all_plan_tmp_test123.grace_date, zh.all_plan_tmp_test123.grace_end_date, zh.all_plan_tmp_test123.prin_amt, zh.all_plan_tmp_test123.due_intr_amt, zh.all_plan_tmp_test123.due_fee_amt, zh.all_plan_tmp_test123.prin_bal, zh.all_plan_tmp_test123.intr_bal, zh.all_plan_tmp_test123.prin_penal_bal, zh.all_plan_tmp_test123.intr_penal_bal, zh.all_plan_tmp_test123.fee_bal, zh.all_plan_tmp_test123.fee_penal_bal, zh.all_plan_tmp_test123.other_fee_bal, zh.all_plan_tmp_test123.prin_paid, zh.all_plan_tmp_test123.intr_paid, zh.all_plan_tmp_test123.prin_penal_paid, zh.all_plan_tmp_test123.intr_penal_paid, zh.all_plan_tmp_test123.fee_paid, zh.all_plan_tmp_test123.fee_penal_paid, zh.all_plan_tmp_test123.other_fee_paid, zh.all_plan_tmp_test123.prin_fund_amt, zh.all_plan_tmp_test123.intr_fund_amt, zh.all_plan_tmp_test123.prin_penal_fund_amt, zh.all_plan_tmp_test123.intr_penal_fund_amt, zh.all_plan_tmp_test123.fee_fund_amt, zh.all_plan_tmp_test123.fee_penal_fund_amt, zh.all_plan_tmp_test123.other_fee_fund_amt, zh.all_plan_tmp_test123.status, zh.all_plan_tmp_test123.rmt_intr_amt, zh.all_plan_tmp_test123.prin_penal_rmt, zh.all_plan_tmp_test123.intr_penal_rmt, zh.all_plan_tmp_test123.fee_rmt, zh.all_plan_tmp_test123.fee_penal_rmt, zh.all_plan_tmp_test123.data_date, zh.all_plan_tmp_test123.other_fee_rmt, zh.all_plan_tmp_test123.billing_date, zh.day_bill_dy_yf.plan_no | N/A     | N/A   |
|         └─HashJoin_56                  | 173360660.79  | 2303812583930.08 | 1135116119 | mpp[tiflash] |               | tiflash_task:{time:3m35.1s, loops:66354, threads:64}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   | inner join, equal:[eq(zh.day_bill_dy_yf.loan_no, zh.all_plan_tmp_test123.loan_no)], stream_count: 80                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            | N/A     | N/A   |
|           ├─ExchangeReceiver_35(Build) | 69937326.00   | 9730120354.37    | 69937326   | mpp[tiflash] |               | tiflash_task:{time:2.45s, loops:6013, threads:64}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | stream_count: 80                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | N/A     | N/A   |
|           │ └─ExchangeSender_34        | 69937326.00   | 5080686921.89    | 69937326   | mpp[tiflash] |               | tiflash_task:{time:3.05s, loops:1139, threads:64}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: zh.day_bill_dy_yf.loan_no, collate: utf8mb4_bin], stream_count: 80                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            | N/A     | N/A   |
|           │   └─Selection_33           | 69937326.00   | 5080686921.89    | 69937326   | mpp[tiflash] |               | tiflash_task:{time:1.31s, loops:1139, threads:64}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | eq(zh.day_bill_dy_yf.create_date, "20240425")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   | N/A     | N/A   |
|           │     └─TableFullScan_32     | 69937326.00   | 4912837339.49    | 69937326   | mpp[tiflash] | table:TDDB    | tiflash_task:{time:1.28s, loops:1139, threads:64}, tiflash_scan:{mvcc_input_rows:0, mvcc_input_bytes:0, mvcc_output_rows:0, lm_skip_rows:0, local_regions:285, remote_regions:0, tot_learner_read:5ms, region_balance:{instance_num: 1, max/min: 285/285=1.000000}, delta_rows:0, delta_bytes:0, segments:79, stale_read_regions:0, tot_build_snapshot:0ms, tot_build_bitmap:0ms, tot_build_inputstream:574ms, min_local_stream:410ms, max_local_stream:1270ms, dtfile:{data_scanned_rows:69937388, data_skipped_rows:491662, mvcc_scanned_rows:0, mvcc_skipped_rows:0, lm_filter_scanned_rows:0, lm_filter_skipped_rows:0, tot_rs_index_check:3ms, tot_read:21817ms}}                                 | pushed down filter:empty, keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | N/A     | N/A   |
|           └─ExchangeReceiver_39(Probe) | 1326987564.00 | 2291769893194.50 | 2682716904 | mpp[tiflash] |               | tiflash_task:{time:2m8.2s, loops:66354, threads:64}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | N/A     | N/A   |
|             └─ExchangeSender_38        | 1326987564.00 | 170195445746.94  | 2682716904 | mpp[tiflash] |               | tiflash_task:{time:1h18m2.3s, loops:67239, threads:64}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: zh.all_plan_tmp_test123.loan_no, collate: utf8mb4_bin]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | N/A     | N/A   |
|               └─Selection_37           | 1326987564.00 | 170195445746.94  | 2682716904 | mpp[tiflash] |               | tiflash_task:{time:3m30.2s, loops:67239, threads:64}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   | eq(zh.all_plan_tmp_test123.data_flag, 9999-12-31 00:00:00.000000), not(isnull(zh.all_plan_tmp_test123.loan_no))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | N/A     | N/A   |
|                 └─TableFullScan_36     | 1326987564.00 | 163825905439.74  | 2682716904 | mpp[tiflash] | table:TDRP    | tiflash_task:{time:3m27.9s, loops:67239, threads:64}, tiflash_scan:{mvcc_input_rows:0, mvcc_input_bytes:0, mvcc_output_rows:0, lm_skip_rows:0, local_regions:10038, remote_regions:0, tot_learner_read:260ms, region_balance:{instance_num: 1, max/min: 10038/10038=1.000000}, delta_rows:0, delta_bytes:0, segments:4615, stale_read_regions:0, tot_build_snapshot:31ms, tot_build_bitmap:0ms, tot_build_inputstream:274757ms, min_local_stream:198690ms, max_local_stream:207490ms, dtfile:{data_scanned_rows:2683002207, data_skipped_rows:63557414, mvcc_scanned_rows:0, mvcc_skipped_rows:0, lm_filter_scanned_rows:0, lm_filter_skipped_rows:0, tot_rs_index_check:17268ms, tot_read:4645186ms}} | pushed down filter:empty, keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | N/A     | N/A   |

The actual rows of table:TDRP is about 1.3 billion, but the query with FastScan returns more than 2.6 billion.

It is because logical split will share DMFiles, but table scanning with FastScan enabled will not filter these duplicate rows out.