Bug#51431 Wrong sort order after import of dump file

The problem is that QUICK_SELECT_DESC behaviour depends
on used_key_parts value which can be bigger than selected
best_key_parts value if an engine supports clustered key.
But used_key_parts is overwritten with best_key_parts
value that prevents from correct selection of index
access method. The fix is to preserve used_key_parts
value for further use in QUICK_SELECT_DESC.

3 files changed, 72 insertions, 1 deletions

diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result
index 2af3db75adc..e2f28b96f7e 100644
--- a/mysql-test/r/innodb_mysql.result
+++ b/mysql-test/r/innodb_mysql.result
@@ -2455,4 +2455,36 @@ AND f5 = 'abcdefghijklmnopwrst' AND f2 = 1221457 AND f4 = 0 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index_merge	PRIMARY,idx1,idx2	idx2,idx1,PRIMARY	7,60,4	NULL	1	Using intersect(idx2,idx1,PRIMARY); Using where
 DROP TABLE t1;
+#
+# Bug#51431 Wrong sort order after import of dump file
+#
+CREATE TABLE t1 (
+f1 INT(11) NOT NULL,
+f2 int(11) NOT NULL,
+f3 int(11) NOT NULL,
+f4 tinyint(1) NOT NULL,
+PRIMARY KEY (f1),
+UNIQUE KEY (f2, f3),
+KEY (f4)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(1,1,991,1), (2,1,992,1), (3,1,993,1), (4,1,994,1), (5,1,995,1),
+(6,1,996,1), (7,1,997,1), (8,1,998,1), (10,1,999,1), (11,1,9910,1),
+(16,1,9911,1), (17,1,9912,1), (18,1,9913,1), (19,1,9914,1), (20,1,9915,1),
+(21,1,9916,1), (22,1,9917,1), (23,1,9918,1), (24,1,9919,1), (25,1,9920,1),
+(26,1,9921,1), (27,1,9922,1);
+FLUSH TABLES;
+SELECT * FROM t1 WHERE f2 = 1 AND f4 = TRUE
+ORDER BY f1 DESC LIMIT 5;
+f1	f2	f3	f4
+27	1	9922	1
+26	1	9921	1
+25	1	9920	1
+24	1	9919	1
+23	1	9918	1
+EXPLAIN SELECT * FROM t1 WHERE f2 = 1 AND f4 = TRUE
+ORDER BY f1 DESC LIMIT 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	f2,f4	f4	1	NULL	11	Using where
+DROP TABLE t1;
 End of 5.1 tests
diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test
index 47135578697..72795392e50 100644
--- a/mysql-test/t/innodb_mysql.test
+++ b/mysql-test/t/innodb_mysql.test
@@ -694,4 +694,34 @@ AND f5 = 'abcdefghijklmnopwrst' AND f2 = 1221457 AND f4 = 0 ;
 
 DROP TABLE t1;
 
+--echo #
+--echo # Bug#51431 Wrong sort order after import of dump file
+--echo #
+
+CREATE TABLE t1 (
+  f1 INT(11) NOT NULL,
+  f2 int(11) NOT NULL,
+  f3 int(11) NOT NULL,
+  f4 tinyint(1) NOT NULL,
+  PRIMARY KEY (f1),
+  UNIQUE KEY (f2, f3),
+  KEY (f4)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES
+(1,1,991,1), (2,1,992,1), (3,1,993,1), (4,1,994,1), (5,1,995,1),
+(6,1,996,1), (7,1,997,1), (8,1,998,1), (10,1,999,1), (11,1,9910,1),
+(16,1,9911,1), (17,1,9912,1), (18,1,9913,1), (19,1,9914,1), (20,1,9915,1),
+(21,1,9916,1), (22,1,9917,1), (23,1,9918,1), (24,1,9919,1), (25,1,9920,1),
+(26,1,9921,1), (27,1,9922,1);
+
+FLUSH TABLES;
+
+SELECT * FROM t1 WHERE f2 = 1 AND f4 = TRUE
+ORDER BY f1 DESC LIMIT 5;
+EXPLAIN SELECT * FROM t1 WHERE f2 = 1 AND f4 = TRUE
+ORDER BY f1 DESC LIMIT 5;
+
+DROP TABLE t1;
+
 --echo End of 5.1 tests
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index b61f7d1eb37..b20726fc151 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -13419,6 +13419,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
     uint nr;
     key_map keys;
     uint best_key_parts= 0;
+    uint saved_best_key_parts= 0;
     int best_key_direction= 0;
     ha_rows best_records= 0;
     double read_time;
@@ -13579,6 +13580,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
             {
               best_key= nr;
               best_key_parts= keyinfo->key_parts;
+              saved_best_key_parts= used_key_parts;
               best_records= quick_records;
               is_best_covering= is_covering;
               best_key_direction= direction; 
@@ -13665,8 +13667,15 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
           */
         }
       }
-      used_key_parts= best_key_parts;
       order_direction= best_key_direction;
+      /*
+        saved_best_key_parts is actual number of used keyparts found by the
+        test_if_order_by_key function. It could differ from keyinfo->key_parts,
+        thus we have to restore it in case of desc order as it affects
+        QUICK_SELECT_DESC behaviour.
+      */
+      used_key_parts= (order_direction == -1) ?
+        saved_best_key_parts :  best_key_parts;
     }
     else
       DBUG_RETURN(0);