Bug #31001: ORDER BY DESC in InnoDB not working

The optimizer sets index traversal in reverse order only if there are 
 used key parts that are not compared to a constant.
However using the primary key as an ORDER BY suffix rendered the check
incomplete : going in reverse order must still be used even if 
all the parts of the secondary key are compared to a constant.

Fixed by relaxing the check and set reverse traversal even when all
the secondary index keyparts are compared to a const.
Also account for the case when all the primary keys are compared to a
constant.


mysql-test/r/innodb_mysql.result:
  Bug #31001: test case
mysql-test/t/innodb_mysql.test:
  Bug #31001: test case
sql/sql_select.cc:
  Bug #31001: 
   - account for the case when all the primary key parts are compared
     to a constant
   - force test_if_skip_sort_order to go backwards over the key even 
     when the number of keyparts used is the same as the number of 
     keyparts equal to a constant. (because of the primary key 
     suffix).

3 files changed, 127 insertions, 1 deletions

diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result
index be678efd0ef..d5f014b6840 100644
--- a/mysql-test/r/innodb_mysql.result
+++ b/mysql-test/r/innodb_mysql.result
@@ -1114,4 +1114,101 @@ c	b
 3	1
 3	2
 DROP TABLE t1;
+CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), INDEX b (b)) ENGINE=InnoDB;
+INSERT INTO t1(a,b) VALUES (1,1), (2,2), (3,2);
+EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a ASC;
+id	1
+select_type	SIMPLE
+table	t1
+type	ref
+possible_keys	b
+key	b
+key_len	5
+ref	const
+rows	1
+Extra	Using where; Using index
+SELECT * FROM t1 WHERE b=2 ORDER BY a ASC;
+a	b
+2	2
+3	2
+EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a DESC;
+id	1
+select_type	SIMPLE
+table	t1
+type	ref
+possible_keys	b
+key	b
+key_len	5
+ref	const
+rows	1
+Extra	Using where; Using index
+SELECT * FROM t1 WHERE b=2 ORDER BY a DESC;
+a	b
+3	2
+2	2
+EXPLAIN SELECT * FROM t1 ORDER BY b ASC, a ASC;
+id	1
+select_type	SIMPLE
+table	t1
+type	index
+possible_keys	NULL
+key	b
+key_len	5
+ref	NULL
+rows	3
+Extra	Using index
+SELECT * FROM t1 ORDER BY b ASC, a ASC;
+a	b
+1	1
+2	2
+3	2
+EXPLAIN SELECT * FROM t1 ORDER BY b DESC, a DESC;
+id	1
+select_type	SIMPLE
+table	t1
+type	index
+possible_keys	NULL
+key	b
+key_len	5
+ref	NULL
+rows	3
+Extra	Using index
+SELECT * FROM t1 ORDER BY b DESC, a DESC;
+a	b
+3	2
+2	2
+1	1
+EXPLAIN SELECT * FROM t1 ORDER BY b ASC, a DESC;
+id	1
+select_type	SIMPLE
+table	t1
+type	index
+possible_keys	NULL
+key	b
+key_len	5
+ref	NULL
+rows	3
+Extra	Using index; Using filesort
+SELECT * FROM t1 ORDER BY b ASC, a DESC;
+a	b
+1	1
+3	2
+2	2
+EXPLAIN SELECT * FROM t1 ORDER BY b DESC, a ASC;
+id	1
+select_type	SIMPLE
+table	t1
+type	index
+possible_keys	NULL
+key	b
+key_len	5
+ref	NULL
+rows	3
+Extra	Using index; Using filesort
+SELECT * FROM t1 ORDER BY b DESC, a ASC;
+a	b
+2	2
+3	2
+1	1
+DROP TABLE t1;
 End of 5.0 tests
diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test
index c39c88096c4..f64efd600c5 100644
--- a/mysql-test/t/innodb_mysql.test
+++ b/mysql-test/t/innodb_mysql.test
@@ -937,4 +937,27 @@ SELECT c,b   FROM t1 GROUP BY c,b;
 
 DROP TABLE t1;
 
+#
+# Bug #31001: ORDER BY DESC in InnoDB not working
+#
+CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), INDEX b (b)) ENGINE=InnoDB;
+INSERT INTO t1(a,b) VALUES (1,1), (2,2), (3,2);
+
+#The two queries below should produce different results, but they don't.
+query_vertical EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a ASC;
+SELECT * FROM t1 WHERE b=2 ORDER BY a ASC;
+query_vertical EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a DESC;
+SELECT * FROM t1 WHERE b=2 ORDER BY a DESC;
+
+query_vertical EXPLAIN SELECT * FROM t1 ORDER BY b ASC, a ASC;
+SELECT * FROM t1 ORDER BY b ASC, a ASC;
+query_vertical EXPLAIN SELECT * FROM t1 ORDER BY b DESC, a DESC;
+SELECT * FROM t1 ORDER BY b DESC, a DESC;
+query_vertical EXPLAIN SELECT * FROM t1 ORDER BY b ASC, a DESC;
+SELECT * FROM t1 ORDER BY b ASC, a DESC;
+query_vertical EXPLAIN SELECT * FROM t1 ORDER BY b DESC, a ASC;
+SELECT * FROM t1 ORDER BY b DESC, a ASC;
+
+DROP TABLE t1;
+
 --echo End of 5.0 tests
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index bd93a7ae203..453bf7c3b63 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -12063,6 +12063,12 @@ static int test_if_order_by_key(ORDER *order, TABLE *table, uint idx,
 
         for (; const_key_parts & 1 ; const_key_parts>>= 1)
           key_part++; 
+        /*
+         The primary and secondary key parts were all const (i.e. there's
+         one row).  The sorting doesn't matter.
+        */
+        if (key_part == key_part_end && reverse == 0)
+          DBUG_RETURN(1);
       }
       else
         DBUG_RETURN(0);
@@ -12480,7 +12486,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
 	  }
 	  DBUG_RETURN(1);
 	}
-	if (tab->ref.key_parts < used_key_parts)
+	if (tab->ref.key_parts <= used_key_parts)
 	{
 	  /*
 	    SELECT * FROM t1 WHERE a=1 ORDER BY a DESC,b DESC