Bug #93049 ORDER BY pk, otherdata should just use PK

Description:
If ORDER BY specifies unique key followed by any other column, optimizer should recognize that anything following unique key /PK in ORDER BY is a noop and not fall back into a filesort, that on a table that has PK of (a), a query like:

SELECT * FROM t OREDR BY a, b

should be rewritten internally to

SELECT * FROM t ORDER BY a

How to repeat:
mysql> select * from t1 order by a, b limit 1;
+---+------+------+------+
| a | b    | c    | d    |
+---+------+------+------+
| 1 |    0 |    1 | NULL |
+---+------+------+------+
1 row in set (0.38 sec)

mysql> explain select * from t1 order by a, b limit 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 522614 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.01 sec)

mysql> show create table t1;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                            |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `d` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `b_c` (`b`,`c`)
) ENGINE=InnoDB AUTO_INCREMENT=786406 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 

Suggested fix:
optimize away useless ORDER BY specifications

Hello Domas,

Thank you for the report and feedback!

Regards,
Umesh