pingcap / pingcap/tidb

Planner: Redundant sort for order by with PK full match

Open
#61,978 0 comments 0 reactions 1 assignee Claimed by @terry1purcell View on GitHub
affects-8.5 affects-9.0 severity/minor 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)

CREATE TABLE `t4` (
`a` int,
`b` int,
`c` int,
Primary KEY `iba` (`a`, `b`),
Unique KEY `iac` (`a`, `c`)
);

Given the above table definition and 2 column PK - if you code the below queries with EQUALs predicates on the 2 PK columns, then the optimizer correctly recognizes that DISTINCT or LIMIT 1 is redundant and removes these from the query - since the PK guarantees at most 1 row. However, if you add ORDER BY - a sort is added, despite there only being a maximum of one row from the PK.

```
tidb> explain select distinct c from t4 where a = 1 and b = 1;
+-------------+---------+------+-----------------------------------------+---------------+
| id | estRows | task | access object | operator info |
+-------------+---------+------+-----------------------------------------+---------------+
| Point_Get_1 | 1.00 | root | table:t4, clustered index:PRIMARY(a, b) | |
+-------------+---------+------+-----------------------------------------+---------------+
1 row in set (0.00 sec)

tidb> explain select c from t4 where a = 1 and b = 1 limit 1;
+-------------+---------+------+-----------------------------------------+---------------+
| id | estRows | task | access object | operator info |
+-------------+---------+------+-----------------------------------------+---------------+
| Point_Get_1 | 1.00 | root | table:t4, clustered index:PRIMARY(a, b) | |
+-------------+---------+------+-----------------------------------------+---------------+
1 row in set (0.00 sec)
```

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

I expect to see NO sort for ORDER BY - such as:

```
tidb> explain select * from t4 where a = 1 and b = 1 order by c;
+-------------+---------+------+-----------------------------------------+---------------+
| id | estRows | task | access object | operator info |
+-------------+---------+------+-----------------------------------------+---------------+
| Point_Get_1 | 1.00 | root | table:t4, clustered index:PRIMARY(a, b) | |
+-------------+---------+------+-----------------------------------------+---------------+
1 row in set (0.00 sec)
```

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

Instead a sort is added:
```
tidb> explain select * from t4 where a = 1 and b = 1 order by c;
+-------------------+---------+------+-----------------------------------------+---------------+
| id | estRows | task | access object | operator info |
+-------------------+---------+------+-----------------------------------------+---------------+
| Sort_5 | 1.00 | root | | test.t4.c |
| └─Point_Get_8 | 1.00 | root | table:t4, clustered index:PRIMARY(a, b) | |
+-------------------+---------+------+-----------------------------------------+---------------+
2 rows in set (0.01 sec)
```

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

Master branch (post 8.5.2)

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.