1 files changed, 98 insertions, 0 deletions
diff --git a/mysql-test/main/order_by_optimizer_innodb.test b/mysql-test/main/order_by_optimizer_innodb.test
new file mode 100644
index 00000000000..90430d11549
--- /dev/null
+++ b/mysql-test/main/order_by_optimizer_innodb.test
@@ -0,0 +1,98 @@
+--source include/have_innodb.inc
+
+--disable_warnings
+drop table if exists t0,t1,t2,t3;
+--enable_warnings
+
+--echo #
+--echo # MDEV-6402: Optimizer doesn't choose best execution plan when composite key is used
+--echo #
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table t1(a int);
+insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C;
+
+CREATE TABLE t2 (
+   pk1 int(11) NOT NULL,
+   pk2 int(11) NOT NULL,
+   fd5 bigint(20) DEFAULT NULL,
+   filler1 char(200),
+   filler2 char(200),
+   PRIMARY KEY (pk1,pk2),
+   UNIQUE KEY ux_pk1_fd5 (pk1,fd5)
+ ) ENGINE=InnoDB;
+
+insert into t2 
+select 
+  round(log(2,t1.a+1)),
+  t1.a,
+  t1.a,
+  REPEAT('filler-data-', 10),
+  REPEAT('filler-data-', 10)
+from 
+  t1;
+
+select pk1, count(*) from t2 group by pk1;
+
+--echo # The following should use range(ux_pk1_fd5), two key parts (key_len=5+8=13)
+EXPLAIN SELECT * FROM t2 USE INDEX(ux_pk1_fd5) WHERE pk1=9 AND fd5 < 500 ORDER BY fd5 DESC LIMIT 10;
+--echo # This also must use range, not ref. key_len must be 13
+EXPLAIN SELECT * FROM t2                       WHERE pk1=9 AND fd5 < 500 ORDER BY fd5 DESC LIMIT 10;
+
+drop table t0,t1, t2;
+
+--echo #
+--echo # MDEV-6814: Server crashes in calculate_key_len on query with ORDER BY
+--echo #
+CREATE TABLE t1 (f1 INT, f2 INT, f3 INT, KEY(f2),KEY(f2,f1)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,5,0),(2,6,0);
+SELECT * FROM t1 WHERE f1 < 3 AND f2 IS NULL ORDER BY f1;
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-6796: Unable to skip filesort when using implicit extended key
+--echo #
+
+CREATE TABLE t1 (
+  pk1 int(11) NOT NULL,
+  pk2 varchar(64) NOT NULL,
+  col1 varchar(16) DEFAULT NULL,
+  PRIMARY KEY (pk1,pk2),
+  KEY key1 (pk1,col1)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+CREATE TABLE t2 (
+  pk1 int(11) NOT NULL,
+  pk2 varchar(64) NOT NULL,
+  col1 varchar(16) DEFAULT NULL,
+  PRIMARY KEY (pk1,pk2),
+  KEY key1 (pk1,col1,pk2)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+INSERT INTO `t1` VALUES 
+(12321321,'a8f5f167f44f4964e6c998dee827110c','video'),
+(12321321,'d77a17a3659ffa60c54e0ea17b6c6d16','video'),
+(12321321,'wwafdsafdsafads','video'),
+(12321321,'696aa249f0738e8181957dd57c2d7d0b','video-2014-09-23'),
+(12321321,'802f9f29584b486f356693e3aa4ef0af','video=sdsd'),
+(12321321,'2f94543ff74aab82e9a058b4e8316d75','video=sdsdsds'),
+(12321321,'c1316b9df0d203fd1b9035308de52a0a','video=sdsdsdsdsd');
+
+insert into t2 select * from t1;
+
+--echo # this must not use filesort:
+explain SELECT pk2
+FROM t1 USE INDEX(key1)
+WHERE pk1 = 123
+AND col1 = 'video'
+ORDER BY pk2 DESC LIMIT 21;
+
+--echo # this must not use filesort, either:
+explain SELECT pk2
+FROM t2 USE INDEX(key1)
+WHERE pk1 = 123 AND col1 = 'video'
+ORDER BY pk2 DESC LIMIT 21;
+
+drop table t1, t2;
+