pingcap / pingcap/tidb

Prefix limit can not be pushed down to tikv when `topn_push_down` is set in `mysql.opt_rule_blacklist`

Open
#67,666 5 comments 0 reactions 1 assignee Claimed by @elsa0520 View on GitHub
severity/moderate sig/planner type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

Please answer these questions before submitting your issue. Thanks!

### 1. Minimal reproduce step (Required)

```sql
CREATE TABLE `t3` (
`c0` text COLLATE utf8mb4_general_ci DEFAULT NULL,
`c1` text COLLATE utf8mb4_general_ci DEFAULT NULL,
KEY `idx1` (`c0`(10))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO mysql.opt_rule_blacklist VALUES("topn_push_down");
ADMIN reload opt_rule_blacklist;

set tidb_opt_partial_ordered_index_for_topn=cost;
explain select /*+ use_index(t3, idx1) */ c0, c1 from t3 order by c0, c1 limit 100 offset 100;
```

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

```
+------------------------------------+----------+-----------+--------------------------+-------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------------+----------+-----------+--------------------------+-------------------------------------------------------------------------------------+
| TopN_7 | 100.00 | root | | test.t3.c0, test.t3.c1, offset:100, count:100, prefix_col:test.t3.c0, prefix_len:10 |
| └─Projection_19 | 200.00 | root | | test.t3.c0, test.t3.c1 |
| └─IndexLookUp_18 | 200.00 | root | | |
| ├─Limit_17(Build) | 200.00 | cop[tikv] | | offset:0, count:200, prefix_col:test.t3.c0, prefix_len:10 |
| │ └─IndexFullScan_15 | 10000.00 | cop[tikv] | table:t3, index:idx1(c0) | keep order:true, stats:pseudo |
| └─TableRowIDScan_16(Probe) | 200.00 | cop[tikv] | table:t3 | keep order:false, stats:pseudo |
+------------------------------------+----------+-----------+--------------------------+-------------------------------------------------------------------------------------+
```

### 3. What did you see instead (Required)

```
+------------------------------------+----------+-----------+--------------------------+--------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------------+----------+-----------+--------------------------+--------------------------------+
| Limit_7 | 100.00 | root | | offset:100, count:100 |
| └─Sort_8 | 200.00 | root | | test.t3.c0, test.t3.c1 |
| └─IndexLookUp_13 | 10000.00 | root | | |
| ├─IndexFullScan_11(Build) | 10000.00 | cop[tikv] | table:t3, index:idx1(c0) | keep order:false, stats:pseudo |
| └─TableRowIDScan_12(Probe) | 10000.00 | cop[tikv] | table:t3 | keep order:false, stats:pseudo |
+------------------------------------+----------+-----------+--------------------------+--------------------------------+
```

### 4. What is your TiDB version? (Required)

nightly(8b8bccf34f1ecf4d5b3a0324e2c0952789c43631)

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.