Descending indexes code exposed a gap in fix for MDEV-25858.

Extend the fix for MDEV-25858 to handle non-reverse-ordered ORDER BY:

If test_if_skip_sort_order() decides to use an index to produce rows
in the required ordering, it should disable "Range Checked for Each Record".

The fix needs to be backported to earlier versions.

3 files changed, 70 insertions, 7 deletions

diff --git a/mysql-test/main/order_by_innodb.result b/mysql-test/main/order_by_innodb.result
index 2038dd479d2..830377f35a3 100644
--- a/mysql-test/main/order_by_innodb.result
+++ b/mysql-test/main/order_by_innodb.result
@@ -180,7 +180,7 @@ id	id
 1	NULL
 2	1
 3	3
-create index for_latest_sort on t2 (d1 desc, d2 desc, id desc);
+create index for_latest_sort on t2 (d1, d2, id);
 select
 t1.id,t2.id
 from
@@ -198,6 +198,36 @@ id	id
 1	NULL
 2	1
 3	3
+# Now, same as above but use a DESC index
+CREATE TABLE t3 (
+id int NOT NULL PRIMARY KEY,
+id2 int NOT NULL,
+d1 datetime,
+d2 timestamp NOT NULL,
+KEY id2 (id2)
+) engine=innodb;
+insert into t3 values
+(1,2,'2019-03-05 00:00:00','2019-03-06 00:00:00'),
+(2,3,'2019-03-05 00:00:00','2019-03-06 00:00:00'),
+(3,3,'2019-03-06 00:00:00','2019-03-05 00:00:00');
+create index for_latest_sort on t3 (d1 desc, d2 desc, id desc);
+select
+t1.id,t3.id
+from
+t1 left join
+t3 on t3.id2 = t1.id and
+t3.id = (select dd.id
+from t3 dd
+where
+dd.id2 = t1.id and
+d1 > '2019-02-06 00:00:00'
+                 order by
+dd.d1 desc, dd.d2 desc, dd.id desc limit 1
+);
+id	id
+1	NULL
+2	1
+3	3
 #
 # MDEV-27270: Wrong query plan with Range Checked for Each Record and ORDER BY ... LIMIT
 #
@@ -220,8 +250,8 @@ dd.d1, dd.d2, dd.id limit 1
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	index	NULL	PRIMARY	4	NULL	#	Using index
 1	PRIMARY	t2	eq_ref	PRIMARY,id2	PRIMARY	4	func	#	Using where
-2	DEPENDENT SUBQUERY	dd	index	id2,for_latest_sort	for_latest_sort	14	NULL	#	Using where
-drop table t1,t2;
+2	DEPENDENT SUBQUERY	dd	range	id2,for_latest_sort	for_latest_sort	6	NULL	#	Using where
+drop table t1,t2,t3;
 # End of 10.2 tests
 #
 # MDEV-26938 Support descending indexes internally in InnoDB
diff --git a/mysql-test/main/order_by_innodb.test b/mysql-test/main/order_by_innodb.test
index 36b2374ba54..19bd1e3007a 100644
--- a/mysql-test/main/order_by_innodb.test
+++ b/mysql-test/main/order_by_innodb.test
@@ -170,7 +170,7 @@ from
                    dd.d1 desc, dd.d2 desc, dd.id desc limit 1
                 );
 
-create index for_latest_sort on t2 (d1 desc, d2 desc, id desc);
+create index for_latest_sort on t2 (d1, d2, id);
 
 select
   t1.id,t2.id
@@ -186,6 +186,38 @@ from
                    dd.d1 desc, dd.d2 desc, dd.id desc limit 1
                 );
 
+--echo # Now, same as above but use a DESC index
+
+CREATE TABLE t3 (
+  id int NOT NULL PRIMARY KEY,
+  id2 int NOT NULL,
+  d1 datetime,
+  d2 timestamp NOT NULL,
+  KEY id2 (id2)
+) engine=innodb;
+
+insert into t3 values
+  (1,2,'2019-03-05 00:00:00','2019-03-06 00:00:00'),
+  (2,3,'2019-03-05 00:00:00','2019-03-06 00:00:00'),
+  (3,3,'2019-03-06 00:00:00','2019-03-05 00:00:00');
+create index for_latest_sort on t3 (d1 desc, d2 desc, id desc);
+
+
+select
+  t1.id,t3.id
+from
+  t1 left join
+  t3 on t3.id2 = t1.id and
+        t3.id = (select dd.id
+                 from t3 dd
+                 where
+                    dd.id2 = t1.id and
+                    d1 > '2019-02-06 00:00:00'
+                 order by
+                   dd.d1 desc, dd.d2 desc, dd.id desc limit 1
+                );
+
+
 --echo #
 --echo # MDEV-27270: Wrong query plan with Range Checked for Each Record and ORDER BY ... LIMIT
 --echo #
@@ -207,7 +239,8 @@ from
                  order by
                    dd.d1, dd.d2, dd.id limit 1
                 );
-drop table t1,t2;
+
+drop table t1,t2,t3;
 
 --echo # End of 10.2 tests
 
diff --git a/mysql-test/suite/engines/funcs/r/ix_using_order.result b/mysql-test/suite/engines/funcs/r/ix_using_order.result
index 645b3fcfbc4..1e4389ce251 100644
--- a/mysql-test/suite/engines/funcs/r/ix_using_order.result
+++ b/mysql-test/suite/engines/funcs/r/ix_using_order.result
@@ -17,7 +17,7 @@ SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `c1` int(11) NOT NULL,
-  KEY `i1` (`c1`) USING BTREE
+  KEY `i1` (`c1` DESC) USING BTREE
 ) ENGINE=ENGINE DEFAULT CHARSET=latin1
 DROP TABLE t1;
 SHOW TABLES;
@@ -40,7 +40,7 @@ SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `c1` int(11) NOT NULL,
-  UNIQUE KEY `i1` (`c1`) USING BTREE
+  UNIQUE KEY `i1` (`c1` DESC) USING BTREE
 ) ENGINE=ENGINE DEFAULT CHARSET=latin1
 DROP INDEX i1 ON t1;
 DROP TABLE t1;