pingcap / pingcap/tidb

The execution plan is incorrect after tidb_enable_ordered_result_mode is enabled

Open
#45,043 2 comments 0 reactions 0 assignees View on GitHub
sig/planner type/feature-request
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report
开启 tidb_enable_ordered_result_mode 后执行计划不对

### 1. Minimal reproduce step (Required)

```sql
MySQL [(none)]> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MySQL [test]> show create table x1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| x1 | CREATE TABLE `x1` (
`id` int(11) NOT NULL,
`k` int(11) DEFAULT NULL,
`c` varchar(100) DEFAULT NULL,
`pad` varchar(100) DEFAULT NULL,
`dt` datetime DEFAULT NULL,
PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
KEY `idx_k` (`k`),
KEY `idx_dt` (`dt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

MySQL [test]> explain select * from x1 order by dt desc limit 10
-> ;
+--------------------------------+---------+-----------+----------------------------+-------------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------------+---------+-----------+----------------------------+-------------------------------------+
| IndexLookUp_18 | 10.00 | root | | limit embedded(offset:0, count:10) |
| ├─Limit_17(Build) | 10.00 | cop[tikv] | | offset:0, count:10 |
| │ └─IndexFullScan_15 | 10.00 | cop[tikv] | table:x1, index:idx_dt(dt) | keep order:true, desc, stats:pseudo |
| └─TableRowIDScan_16(Probe) | 10.00 | cop[tikv] | table:x1 | keep order:false, stats:pseudo |
+--------------------------------+---------+-----------+----------------------------+-------------------------------------+
4 rows in set (0.00 sec)

MySQL [test]> set tidb_enable_ordered_result_mode=1;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> explain select * from x1 order by dt desc limit 10;
+----------------------------+----------+-----------+---------------+------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+----------------------------+----------+-----------+---------------+------------------------------------------------------------------------------------+
| TopN_7 | 10.00 | root | | test.x1.dt:desc, test.x1.id, test.x1.k, test.x1.c, test.x1.pad, offset:0, count:10 |
| └─TableReader_14 | 10.00 | root | | data:TopN_13 |
| └─TopN_13 | 10.00 | cop[tikv] | | test.x1.dt:desc, test.x1.id, test.x1.k, test.x1.c, test.x1.pad, offset:0, count:10 |
| └─TableFullScan_12 | 10000.00 | cop[tikv] | table:x1 | keep order:false, stats:pseudo |
+----------------------------+----------+-----------+---------------+------------------------------------------------------------------------------------+
4 rows in set (0.01 sec)
```

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

### 3. What did you see instead (Required)
tablefullscan + topn

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

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.