Fixed MDEV-5424: SELECT using ORDER BY DESC and LIMIT produces unexpected results (InnoDB/XtraDB)

This only happend when using an ORDER BY on a primary key part, where all other key parts where constant.
Remove of duplicated expressions in ORDER BY (as the old code did this in some strange cases)


mysql-test/r/group_by.result:
  Fixed results to take into account that duplicate order by parts are now deleted
mysql-test/r/group_by_innodb.result:
  Ensure extended keys are on
mysql-test/r/innodb_ext_key.result:
  More tests
mysql-test/r/order_by.result:
  More tests
mysql-test/t/group_by.test:
  Fixed results to take into account that duplicate order by parts are now deleted
mysql-test/t/group_by_innodb.test:
  Ensure extended keys are on
mysql-test/t/innodb_ext_key.test:
  More tests
mysql-test/t/order_by.test:
  More tests
sql/sql_select.cc:
  Fixed bug where we looked at extended key parts when we shouldn't
  Remove of duplicated expressions in ORDER BY
sql/table.cc:
  Indentation fixes

4 files changed, 45 insertions, 6 deletions

diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
index 1ef514d591d..e4120f284de 100644
--- a/mysql-test/t/group_by.test
+++ b/mysql-test/t/group_by.test
@@ -1336,7 +1336,7 @@ INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
    (11),(12),(13),(14),(15),(16),(17),(18),(19),(20);
 
 let $query0=SELECT col1 AS field1, col1 AS field2
-            FROM t1 GROUP BY field1, field2;
+            FROM t1 GROUP BY field1, field2+0;
 
 # Needs to be range to exercise bug
 --eval EXPLAIN $query0;
@@ -1366,7 +1366,9 @@ LIMIT 3;
 
 explain
 select col1 f1, col1 f2 from t1 order by f2, f1;
-select col1 f1, col1 f2 from t1 order by f2, f1;
+explain
+select col1 f1, col1 f2 from t1 order by f2, f1+0;
+select col1 f1, col1 f2 from t1 order by f2, f1+0;
 
 explain
 select col1 f1, col1 f2 from t1 group by f2 order by f2, f1;
@@ -1386,12 +1388,12 @@ INSERT INTO t2(col1, col2) VALUES
    (11,10),(12,9),(13,8),(14,7),(15,6),(16,5),(17,4),(18,3),(19,2),(20,1);
 
 explain
-select col1 f1, col2 f2, col1 f3 from t2 group by f1, f2, f3;
-select col1 f1, col2 f2, col1 f3 from t2 group by f1, f2, f3;
+select col1 f1, col2 f2, col1 f3 from t2 group by f1, f2, f3+0;
+select col1 f1, col2 f2, col1 f3 from t2 group by f1, f2, f3+0;
 
 explain
-select col1 f1, col2 f2, col1 f3 from t2 order by f1, f2, f3;
-select col1 f1, col2 f2, col1 f3 from t2 order by f1, f2, f3;
+select col1 f1, col2 f2, col1 f3 from t2 order by f1, f2, f3+0;
+select col1 f1, col2 f2, col1 f3 from t2 order by f1, f2, f3+0;
 
 DROP VIEW v1;
 DROP TABLE t1, t2;
diff --git a/mysql-test/t/group_by_innodb.test b/mysql-test/t/group_by_innodb.test
index 0d5e5e9ae30..9733610dde1 100644
--- a/mysql-test/t/group_by_innodb.test
+++ b/mysql-test/t/group_by_innodb.test
@@ -4,6 +4,8 @@
 
 --source include/have_innodb.inc
 
+set @save_ext_key_optimizer_switch=@@optimizer_switch;
+
 --echo #
 --echo # MDEV-3992 Server crash or valgrind errors in test_if_skip_sort_order/test_if_cheaper_ordering
 --echo # on GROUP BY with indexes on InnoDB table
@@ -15,6 +17,8 @@ CREATE TABLE t1 (
   KEY (pk)
 ) ENGINE=InnoDB;
 
+set optimizer_switch='extended_keys=on';
+
 INSERT INTO t1 VALUES (1,'a'),(2,'b');
 
 EXPLAIN
@@ -36,3 +40,5 @@ WHERE a = 'r' OR pk = 183 GROUP BY field1, field1;
 drop table t1;
 
 --echo End of 5.5 tests
+
+set optimizer_switch=@save_ext_key_optimizer_switch;
diff --git a/mysql-test/t/innodb_ext_key.test b/mysql-test/t/innodb_ext_key.test
index 36414105917..6d1890ac1f9 100644
--- a/mysql-test/t/innodb_ext_key.test
+++ b/mysql-test/t/innodb_ext_key.test
@@ -662,6 +662,34 @@ ORDER BY rev_timestamp ASC LIMIT 10;
 
 DROP TABLE t1,t2,t3;
 
+--echo #
+--echo # MDEV-5424 SELECT using ORDER BY DESC and LIMIT produces unexpected
+--echo # results (InnoDB/XtraDB)
+--echo #
+
+create table t1 (a bigint not null unique auto_increment, b varchar(10), primary key (a), key (b(2))) engine = myisam default character set utf8;
+create table t2 (a bigint not null unique auto_increment, b varchar(10), primary key (a), key (b(2))) engine = innodb default character set utf8;
+
+insert into t1 (b) values (null), (null), (null);
+insert into t2 (b) values (null), (null), (null);
+
+set optimizer_switch='extended_keys=on';
+explain select a from t1 where b is null order by a desc limit 2;
+select a from t1 where b is null order by a desc limit 2;
+explain select a from t2 where b is null order by a desc limit 2;
+select a from t2 where b is null order by a desc limit 2;
+set optimizer_switch='extended_keys=off';
+explain select a from t2 where b is null order by a desc limit 2;
+select a from t2 where b is null order by a desc limit 2;
+
+explain select a from t2 where b is null order by a desc;
+select a from t2 where b is null order by a desc;
+
+explain select a from t2 where b is null order by a desc,a,a;
+select a from t2 where b is null order by a desc,a,a;
+
+drop table t1, t2;
+
 set optimizer_switch=@save_optimizer_switch;
 
 set optimizer_switch=@save_ext_key_optimizer_switch;
diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test
index 3e20d22d726..35f1a7f744b 100644
--- a/mysql-test/t/order_by.test
+++ b/mysql-test/t/order_by.test
@@ -261,6 +261,9 @@ desc,b desc;
 explain select * from t1 where a = 2 and b > 0 order by a desc,b desc;
 explain select * from t1 where a = 2 and b < 2 order by a desc,b desc;
 explain select * from t1 where a = 1 order by b desc;
+explain select * from t1 where a = 2 and b > 0 order by a desc,b desc,b,a;
+explain select * from t1 where a = 2 and b < 2 order by a desc,a,b desc,a,b;
+
 select * from t1 where a = 1 order by b desc;
 #
 # Test things when we don't have NULL keys