planner: redundant sort when making sql plan

[xuyifangreeneyes]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table t(a int, b int, c int, d int, index idx_a_b_c(a, b, c));
explain select a, b, c from t where a > 3 and b = 4 order by a, c;

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

idx_a_b_c matches the physical property that order by a, c asks and no sort is needed. Here is the plan made by MySQL

mysql> explain select a, b, c from t where a > 3 and b = 4 order by a, c;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t     | NULL       | index | idx_a_b_c     | idx_a_b_c | 15      | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.01 sec)

3. What did you see instead (Required)

The plan made by TiDB has redundant sort.

mysql> explain select a, b, c from t where a > 3 and b = 4 order by a, c;
+----------------------------+---------+-----------+-----------------------------------+------------------------------------------------+
| id                         | estRows | task      | access object                     | operator info                                  |
+----------------------------+---------+-----------+-----------------------------------+------------------------------------------------+
| Sort_5                     | 3.33    | root      |                                   | test.t.a, test.t.c                             |
| └─IndexReader_10           | 3.33    | root      |                                   | index:Selection_9                              |
|   └─Selection_9            | 3.33    | cop[tikv] |                                   | eq(test.t.b, 4)                                |
|     └─IndexRangeScan_8     | 3333.33 | cop[tikv] | table:t, index:idx_a_b_c(a, b, c) | range:(3,+inf], keep order:false, stats:pseudo |
+----------------------------+---------+-----------+-----------------------------------+------------------------------------------------+
4 rows in set (0.00 sec)

4. What is your TiDB version? (Required)

mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v5.2.0-alpha-222-g297a90dde
Edition: Community
Git Commit Hash: 297a90dde2018c53bc38b08605c4460ce4ccdbc6
Git Branch: master
UTC Build Time: 2021-07-07 05:52:03
GoVersion: go1.16.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.01 sec)

[xuyifangreeneyes]

/assign