Fixed bug#35844.

The function test_if_skip_sort_order ignored any covering index used for ref
access of a table in a query with ORDER BY if this index was incompatible 
with the ORDER BY list and there was another covering index compatible with
this list. 
As a result sub-optimal execution plans were chosen for some queries with
ORDER BY clause. 


mysql-test/r/distinct.result:
  Adjusted results after the fix for bug#35844.
mysql-test/r/order_by.result:
  Added a test case for bug#35844.
mysql-test/t/order_by.test:
  Added a test case for bug#35844.

3 files changed, 59 insertions, 1 deletions

diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result
index 15e4c3f15b3..1fbda27f5da 100644
--- a/mysql-test/r/distinct.result
+++ b/mysql-test/r/distinct.result
@@ -694,7 +694,7 @@ INSERT INTO t1(a, b, c) VALUES (1, 1, 1),
 (1, 2, 3);
 EXPLAIN SELECT DISTINCT a, b, d, c FROM t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	a	16	NULL	6	Using index
+1	SIMPLE	t1	range	NULL	PRIMARY	16	NULL	7	Using index for group-by; Using temporary
 SELECT DISTINCT a, b, d, c FROM t1;
 a	b	d	c
 1	1	0	1
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index faab8e49880..e70ce75797d 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -1458,3 +1458,29 @@ ORDER BY t2.c LIMIT 1;
 d
 52.5
 DROP TABLE t1,t2,t3;
+CREATE TABLE t1 (
+id1 INT NULL,
+id2 INT  NOT NULL,
+junk INT NOT NULL,
+PRIMARY KEY (id1, id2, junk),
+INDEX id2_j_id1 (id2, junk, id1)
+);
+INSERT INTO t1 VALUES (1, 1, 1), (2, 1, 2), (3, 1, 3), (4, 1, 4);
+INSERT INTO t1 VALUES (5, 2, 1), (6, 2, 2), (7, 2, 3), (8, 2, 4);
+INSERT INTO t1 VALUES (9, 3, 1), (10, 3, 2), (11, 3, 3), (12, 3, 4);
+INSERT INTO t1 VALUES (13, 4, 1), (14, 4, 2), (15, 4, 3), (16, 4, 4);
+INSERT INTO t1 VALUES (17, 5, 1), (18, 5, 2), (19, 5, 3), (20, 5, 4);
+INSERT INTO t1 VALUES (21, 6, 1), (22, 6, 2), (23, 6, 3), (24, 6, 4);
+INSERT INTO t1 VALUES (25, 7, 1), (26, 7, 2), (27, 7, 3), (28, 7, 4);
+INSERT INTO t1 VALUES (29, 8, 1), (30, 8, 2), (31, 8, 3), (32, 8, 4);
+INSERT INTO t1 VALUES (33, 9, 1), (34, 9, 2), (35, 9, 3), (36, 9, 4);
+EXPLAIN SELECT id1 FROM t1 WHERE id2 = 4 ORDER BY id1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	id2_j_id1	id2_j_id1	4	const	4	Using where; Using index; Using filesort
+SELECT id1 FROM t1 WHERE id2 = 4 ORDER BY id1;
+id1
+13
+14
+15
+16
+DROP TABLE t1;
diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test
index 64665c6b5bd..bf4cc37a80f 100644
--- a/mysql-test/t/order_by.test
+++ b/mysql-test/t/order_by.test
@@ -1309,3 +1309,35 @@ WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
 ORDER BY t2.c LIMIT 1;
 
 DROP TABLE t1,t2,t3;
+
+
+#
+# Bug#35844: Covering index for ref access not compatible with ORDER BY list
+#
+
+CREATE TABLE t1 (
+  id1 INT NULL,
+  id2 INT  NOT NULL,
+  junk INT NOT NULL,
+  PRIMARY KEY (id1, id2, junk),
+  INDEX id2_j_id1 (id2, junk, id1)
+);
+
+INSERT INTO t1 VALUES (1, 1, 1), (2, 1, 2), (3, 1, 3), (4, 1, 4);
+INSERT INTO t1 VALUES (5, 2, 1), (6, 2, 2), (7, 2, 3), (8, 2, 4);
+INSERT INTO t1 VALUES (9, 3, 1), (10, 3, 2), (11, 3, 3), (12, 3, 4);
+INSERT INTO t1 VALUES (13, 4, 1), (14, 4, 2), (15, 4, 3), (16, 4, 4);
+INSERT INTO t1 VALUES (17, 5, 1), (18, 5, 2), (19, 5, 3), (20, 5, 4);
+INSERT INTO t1 VALUES (21, 6, 1), (22, 6, 2), (23, 6, 3), (24, 6, 4);
+INSERT INTO t1 VALUES (25, 7, 1), (26, 7, 2), (27, 7, 3), (28, 7, 4);
+INSERT INTO t1 VALUES (29, 8, 1), (30, 8, 2), (31, 8, 3), (32, 8, 4);
+INSERT INTO t1 VALUES (33, 9, 1), (34, 9, 2), (35, 9, 3), (36, 9, 4);
+
+EXPLAIN SELECT id1 FROM t1 WHERE id2 = 4 ORDER BY id1;
+
+SELECT id1 FROM t1 WHERE id2 = 4 ORDER BY id1;
+
+DROP TABLE t1;
+
+
+