Limit exec return enough data but not stop

[King-Dylan]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `test` (
  `user_id` bigint(20) NOT NULL,
  `no` bigint(20) NOT NULL,
  `value` bigint(20) NOT NULL DEFAULT '0',
  `deadline` bigint(20) NOT NULL,
  `delete` bigint(20) NOT NULL,
  PRIMARY KEY (`user_id`,`no`,`delete`) /*T![clustered_index] CLUSTERED */,
  KEY `idx` (`user_id`,`deadline`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY RANGE (`delete`)
(PARTITION `p0` VALUES LESS THAN (946652400),
 PARTITION `pN` VALUES LESS THAN (1675177200),
 PARTITION `pmax` VALUES LESS THAN (MAXVALUE));

INSERT INTO `test` VALUES (1,1,0,1672785876,1672817268),(1,2,0,1672716570,1672730573)...## 20w rows
mysql:[test] 15:57:58> trace select * from present use index(idx) where user_id = 1 and delete_time = 0 order by deadline_time limit 100;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+--------------+
| operation                                                                                                                                                                                                  | startTS         | duration     |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+--------------+
| trace                                                                                                                                                                                                      | 15:58:21.327414 | 3.517968708s |
|   ├─session.ExecuteStmt                                                                                                                                                                                    | 15:58:21.327445 | 41.498541ms  |
|   │ ├─executor.Compile                                                                                                                                                                                     | 15:58:21.327480 | 842.458µs    |
|   │ └─session.runStmt                                                                                                                                                                                      | 15:58:21.328411 | 40.439667ms  |
|   ├─*executor.TopNExec.Next                                                                                                                                                                                | 15:58:21.368972 | 3.475261416s |
|   │ ├─*executor.IndexLookUpExecutor.Next                                                                                                                                                                   | 15:58:21.368999 | 93.401375ms  |
|   │ │ ├─distsql.Select                                                                                                                                                                                     | 15:58:21.369081 | 67.875µs     |
|   │ │ │ └─regionRequest.SendReqCtx                                                                                                                                                                         | 15:58:21.369241 | 42.849583ms  |
|   │ │ │   ├─rpcClient.SendRequest, region ID: 241, type: Cop                                                                                                                                               | 15:58:21.369332 | 42.710166ms  |
|   │ │ │   │ └─tikv.RPC                                                                                                                                                                                     | 15:58:21.369335 | 1.810151ms   |
|   │ │ │   │   └─tikv.Wait                                                                                                                                                                                  | 15:58:21.369335 | 883.65µs     |
|   │ │ │   │     └─tikv.GetSnapshot                                                                                                                                                                         | 15:58:21.369335 | 883.65µs     |
|   │ │ │   └─regionRequest.SendReqCtx                                                                                                                                                                       | 15:58:21.412184 | 42.798375ms  |
|   │ │ │     ├─rpcClient.SendRequest, region ID: 241, type: Cop                                                                                                                                             | 15:58:21.412217 | 42.694709ms  |
|   │ │ │     │ └─tikv.RPC                                                                                                                                                                                   | 15:58:21.412219 | 904.452µs    |
|   │ │ │     │   └─tikv.Wait                                                                                                                                                                                | 15:58:21.412219 | 44.051µs     |
|   │ │ │     │     └─tikv.GetSnapshot                                                                                                                                                                       | 15:58:21.412219 | 44.051µs     |
|   │ │ │     └─regionRequest.SendReqCtx                                                                                                                                                                     | 15:58:21.455149 | 42.978208ms  |
|   │ │ │       ├─rpcClient.SendRequest, region ID: 241, type: Cop                                                                                                                                           | 15:58:21.455201 | 42.844792ms  |
|   │ │ │       │ └─tikv.RPC                                                                                                                                                                                 | 15:58:21.455208 | 819.15µs     |
|   │ │ │       │   └─tikv.Wait                                                                                                                                                                              | 15:58:21.455208 | 36.964µs     |
|   │ │ │       │     └─tikv.GetSnapshot                                                                                                                                                                     | 15:58:21.455208 | 36.964µs     |
|   │ │ │       └─regionRequest.SendReqCtx                                                                                                                                                                   | 15:58:21.498305 | 47.758459ms  |
|   │ │ │         ├─rpcClient.SendRequest, region ID: 241, type: Cop                                                                                                                                         | 15:58:21.498372 | 47.621042ms  |
|   │ │ │         │ └─tikv.RPC                                                                                                                                                                               | 15:58:21.498377 | 849.319µs    |
|   │ │ │         │   └─tikv.Wait                                                                                                                                                                            | 15:58:21.498377 | 34.13µs      |
|   │ │ │         │     └─tikv.GetSnapshot                                                                                                                                                                   | 15:58:21.498377 | 34.13µs      |
|   │ │ │         └─regionRequest.SendReqCtx                                                                                                                                                                 | 15:58:21.546204 | 43.92175ms   |
|   │ │ │           ├─rpcClient.SendRequest, region ID: 241, type: Cop                                                                                                                                       | 15:58:21.546261 | 43.826583ms  |
|   │ │ │           │ └─tikv.RPC                                                                                                                                                                             | 15:58:21.546266 | 894.285µs    |
|   │ │ │           │   └─tikv.Wait                                                                                                                                                                          | 15:58:21.546266 | 34.774µs     |
|   │ │ │           │     └─tikv.GetSnapshot                                                                                                                                                                 | 15:58:21.546266 | 34.774µs     |
|   │ │ │           └─regionRequest.SendReqCtx                                                                                                                                                               | 15:58:21.590223 | 43.448083ms  |
|   │ │ │             ├─rpcClient.SendRequest, region ID: 241, type: Cop                                                                                                                                     | 15:58:21.590262 | 43.367ms     |
|   │ │ │             │ └─tikv.RPC                                                                                                                                                                           | 15:58:21.590268 | 815.745µs    |
|   │ │ │             │   └─tikv.Wait                    
.........

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

mysql:[test] 16:16:54> explain analyze select * from test use index(idx) where user_id = 1 and delete = 0 order by deadline limit 100;
+----------------------------------+----------+---------+-----------+--------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------+---------+------+
| id                               | estRows  | actRows | task      | access object                                    | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                        | operator info                                   | memory  | disk |
+----------------------------------+----------+---------+-----------+--------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------+---------+------+
| TopN_9                           | 100.00   | 100     | root      |                                                  | time:181.6ms, loops:2                                                                                                                                                                                                                                                                                                                                                                                                                                 | test.present.deadline, offset:0, count:100 | 18.6 KB | N/A  |
| └─IndexLookUp_17                 | 100.00   | 100     | root      | partition:p0                                     | time:181.2ms, loops:2, index_task: {total_time: 43ms, fetch_handle: 43ms, build: 11.5µs, wait: 13.8µs}, table_task: {total_time: 97.1ms, num: 1, concurrency: 5}                                                                                                                                                                                                                                                                                      |                                                 | 49.8 KB | N/A  |
|   ├─Limit_16(Build)              | 100.00   | 100     | cop[tikv] |                                                  | time:42.4ms, loops:2, cop_task: {num: 1, max: 42.1ms, proc_keys: 224, rpc_num: 1, rpc_time: 42ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}, tikv_task:{time:1ms, loops:3}, scan_detail: {total_process_keys: 224, total_process_keys_size: 18816, total_keys: 225, get_snapshot_time: 53.2µs, rocksdb: {key_skipped_count: 224, block: {cache_hit_count: 2}}}                                                                             | offset:0, count:100                             | N/A     | N/A  |
|   │ └─Selection_15               | 16572.60 | 224     | cop[tikv] |                                                  | tikv_task:{time:1ms, loops:3}                                                                                                                                                                                                                                                                                                                                                                                                                         | eq(test.present.delete, 0)                 | N/A     | N/A  |
|   │   └─IndexRangeScan_13        | 22470.59 | 224     | cop[tikv] | table:test, index:idx(user_id, deadline) | tikv_task:{time:0s, loops:3}                                                                                                                                                                                                                                                                                                                                                                                                                          | range:[1,1], keep order:false                   | N/A     | N/A  |
|   └─TableRowIDScan_14(Probe)     | 100.00   | 100     | cop[tikv] | table:test                                    | time:51.8ms, loops:2, cop_task: {num: 1, max: 51.1ms, proc_keys: 100, tot_proc: 5ms, rpc_num: 1, rpc_time: 50.9ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}, tikv_task:{time:6ms, loops:3}, scan_detail: {total_process_keys: 100, total_process_keys_size: 6200, total_keys: 200, get_snapshot_time: 39.2µs, rocksdb: {key_skipped_count: 100, block: {cache_hit_count: 185, read_count: 15, read_byte: 70.8 KB, read_time: 103.4µs}}}   | keep order:false                                | N/A     | N/A  |
+----------------------------------+----------+---------+-----------+--------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------+---------+------+
6 rows in set (0.23 sec)

3. What did you see instead (Required)

mysql:[test] 16:17:05> explain analyze select * from test use index(idx) where user_id = 1 and delete = 0 order by deadline limit 100;
+----------------------------------+----------+---------+-----------+--------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------+----------+------+
| id                               | estRows  | actRows | task      | access object                                    | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | operator info                                   | memory   | disk |
+----------------------------------+----------+---------+-----------+--------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------+----------+------+
| TopN_9                           | 100.00   | 100     | root      |                                                  | time:3.47s, loops:2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   | test.present.deadline, offset:0, count:100 | 18.1 KB  | N/A  |
| └─IndexLookUp_17                 | 100.00   | 6700    | root      | partition:p0                                     | time:3.46s, loops:9, index_task: {total_time: 2.95s, fetch_handle: 2.95s, build: 31.1µs, wait: 82.1µs}, table_task: {total_time: 1.51s, num: 7, concurrency: 5}                                                                                                                                                                                                                                                                                                                                                                                                       |                                                 | 422.3 KB | N/A  |
|   ├─Limit_16(Build)              | 100.00   | 6700    | cop[tikv] |                                                  | time:2.92s, loops:13, cop_task: {num: 67, max: 48ms, min: 41.8ms, avg: 43.9ms, p95: 46.2ms, max_proc_keys: 224, p95_proc_keys: 224, rpc_num: 67, rpc_time: 2.94s, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}, tikv_task:{proc max:1ms, min:0s, avg: 417.9µs, p80:1ms, p95:1ms, iters:201, tasks:67}, scan_detail: {total_process_keys: 15000, total_process_keys_size: 1260000, total_keys: 15067, get_snapshot_time: 3.42ms, rocksdb: {key_skipped_count: 15000, block: {cache_hit_count: 134, read_count: 17, read_byte: 80.8 KB, read_time: 234.6µs}}}   | offset:0, count:100                             | N/A      | N/A  |
|   │ └─Selection_15               | 16572.60 | 15000   | cop[tikv] |                                                  | tikv_task:{proc max:1ms, min:0s, avg: 417.9µs, p80:1ms, p95:1ms, iters:201, tasks:67}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | eq(test.present.delete, 0)                 | N/A      | N/A  |
|   │   └─IndexRangeScan_13        | 22470.59 | 15000   | cop[tikv] | table:test, index:idx(user_id, deadline) | tikv_task:{proc max:1ms, min:0s, avg: 373.1µs, p80:1ms, p95:1ms, iters:201, tasks:67}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | range:[1,1], keep order:false                   | N/A      | N/A  |
|   └─TableRowIDScan_14(Probe)     | 100.00   | 6700    | cop[tikv] | table:test                                   | time:1.5s, loops:18, cop_task: {num: 7, max: 687ms, min: 44.9ms, avg: 213.3ms, p95: 687ms, max_proc_keys: 3200, p95_proc_keys: 3200, tot_proc: 141ms, rpc_num: 7, rpc_time: 1.49s, copr_cache_hit_ratio: 0.14, distsql_concurrency: 15}, tikv_task:{proc max:65ms, min:6ms, avg: 21.4ms, p80:37ms, p95:65ms, iters:33, tasks:7}, scan_detail: {total_process_keys: 6600, total_process_keys_size: 409200, total_keys: 13200, get_snapshot_time: 564.1µs, rocksdb: {key_skipped_count: 6600, block: {cache_hit_count: 13200}}}                                         | keep order:false                                | N/A      | N/A  |
+----------------------------------+----------+---------+-----------+--------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------+----------+------+
6 rows in set (3.64 sec)

4. What is your TiDB version? (Required)

Master

[XuHuaiyu]

refer: #40471