Description:
The index hint FORCE INDEX FOR ORDER BY/GROUP BY affects single-table queries, but is ignored for some join queries with ORDER BY when an index is forced to be used to access rows of the first join table.
How to repeat:
The following test case demonstrates the problem:
create table t1 (a int, b int, index idx(a));
insert into t1 values (5,50), (3,30), (3,33), (7,77), (5,55), (5,51), (3,31);
create table t2 (b int, c int, index idx(b));
insert into t2 values (55,551), (33,333), (55,552), (55,555), (33,337), (55,556), (33,332);
insert into t2 values (50,551), (30,333), (50,552), (50,555), (30,337), (50,556), (30,332);
mysql> explain select * from t1 order by a;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 7 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)
mysql> explain select * from t1 force index for order by (idx) order by a;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | t1 | index | NULL | idx | 5 | NULL | 7 | |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
mysql> explain select t1.a, t1.b, t2.c from t1,t2 where t1.b=t2.b order by a;
+----+-------------+-------+------+---------------+------+---------+-----------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-----------+------+----------------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 7 | Using filesort |
| 1 | SIMPLE | t2 | ref | idx | idx | 5 | test.t1.b | 2 | Using where |
+----+-------------+-------+------+---------------+------+---------+-----------+------+----------------+
2 rows in set (0.00 sec)
mysql> explain select t1.a, t1.b, t2.c from t1 force index for order by (idx), t2 where t1.b=t2.b order by a;
+----+-------------+-------+------+---------------+------+---------+-----------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-----------+------+----------------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 7 | Using filesort |
| 1 | SIMPLE | t2 | ref | idx | idx | 5 | test.t1.b | 2 | Using where |
+----+-------------+-------+------+---------------+------+---------+-----------+------+----------------+
2 rows in set (0.00 sec)
