Merge ../10.1-orderby-fixes into 10.1

13 files changed, 217 insertions, 21 deletions

diff --git a/mysql-test/r/myisam_explain_non_select_all.result b/mysql-test/r/myisam_explain_non_select_all.result
index 285a1ca6786..a9eeee8548c 100644
--- a/mysql-test/r/myisam_explain_non_select_all.result
+++ b/mysql-test/r/myisam_explain_non_select_all.result
@@ -674,14 +674,14 @@ FLUSH STATUS;
 FLUSH TABLES;
 EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a > 0 ORDER BY t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	index	PRIMARY	PRIMARY	4	NULL	3	100.00	Using where; Using index
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	3	100.00	Using where; Using index
 Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > 0) order by `test`.`t1`.`a`
 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution
 Variable_name	Value
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
-Handler_read_first	1
+Handler_read_key	1
 Handler_read_next	3
 # Status of testing query execution:
 Variable_name	Value
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index 28a276d16c1..d5b25534de0 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -297,7 +297,7 @@ create table t1 (a int not null, b int, c varchar(10), key (a, b, c));
 insert into t1 values (1, NULL, NULL), (1, NULL, 'b'), (1, 1, NULL), (1, 1, 'b'), (1, 1, 'b'), (2, 1, 'a'), (2, 1, 'b'), (2, 2, 'a'), (2, 2, 'b'), (2, 3, 'c'),(1,3,'b');
 explain select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	a	a	22	NULL	11	Using where; Using index
+1	SIMPLE	t1	range	a	a	22	NULL	2	Using where; Using index
 select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc;
 a	b	c
 1	NULL	b
@@ -2569,7 +2569,7 @@ SELECT * FROM t1 r JOIN t1 s ON r.a = s.a
 WHERE s.a IN (2,9) OR s.a < 100  AND s.a != 0
 ORDER BY 1 LIMIT 10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	r	index	PRIMARY	PRIMARY	4	NULL	10	100.00	Using where; Using index
+1	SIMPLE	r	range	PRIMARY	PRIMARY	4	NULL	12	100.00	Using where; Using index
 1	SIMPLE	s	eq_ref	PRIMARY	PRIMARY	4	test.r.a	1	100.00	Using index
 Warnings:
 Note	1003	select `test`.`r`.`a` AS `a`,`test`.`s`.`a` AS `a` from `test`.`t1` `r` join `test`.`t1` `s` where ((`test`.`s`.`a` = `test`.`r`.`a`) and ((`test`.`r`.`a` in (2,9)) or ((`test`.`r`.`a` < 100) and (`test`.`r`.`a` <> 0)))) order by 1 limit 10
@@ -2600,7 +2600,7 @@ CREATE TABLE t1 (a INT,KEY (a));
 INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
 EXPLAIN SELECT DISTINCT a,1 FROM t1 WHERE a <> 1 ORDER BY a DESC;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	a	a	5	NULL	10	Using where; Using index; Using filesort
+1	SIMPLE	t1	range	a	a	5	NULL	10	Using where; Using index
 SELECT DISTINCT a,1 FROM t1 WHERE a <> 1 ORDER BY a DESC;
 a	1
 10	1
diff --git a/mysql-test/r/order_by_optimizer_innodb.result b/mysql-test/r/order_by_optimizer_innodb.result
new file mode 100644
index 00000000000..f3167db4b9a
--- /dev/null
+++ b/mysql-test/r/order_by_optimizer_innodb.result
@@ -0,0 +1,98 @@
+drop table if exists t0,t1,t2,t3;
+#
+# MDEV-6402: Optimizer doesn't choose best execution plan when composite key is used
+#
+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;
+pk1	count(*)
+0	1
+1	1
+2	3
+3	6
+4	11
+5	23
+6	45
+7	91
+8	181
+9	362
+10	276
+# 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;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	range	ux_pk1_fd5	ux_pk1_fd5	13	NULL	137	Using where
+# 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;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	range	PRIMARY,ux_pk1_fd5	ux_pk1_fd5	13	NULL	137	Using where
+drop table t0,t1, t2;
+#
+# MDEV-6814: Server crashes in calculate_key_len on query with ORDER BY
+#
+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;
+f1	f2	f3
+DROP TABLE t1;
+#
+# MDEV-6796: Unable to skip filesort when using implicit extended key
+#
+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;
+# 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;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	key1	key1	55	const,const	1	Using where; Using index
+# 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;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ref	key1	key1	55	const,const	1	Using where; Using index
+drop table t1, t2;
diff --git a/mysql-test/r/show_explain.result b/mysql-test/r/show_explain.result
index 3695384bac4..8aefb552d57 100644
--- a/mysql-test/r/show_explain.result
+++ b/mysql-test/r/show_explain.result
@@ -1036,7 +1036,7 @@ explain
 SELECT b AS field1, b AS field2 FROM t1, t2, t3 WHERE d = b ORDER BY field1, field2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	1	
-1	SIMPLE	t1	index	b	b	6	NULL	107	Using where; Using index
+1	SIMPLE	t1	range	b	b	6	NULL	107	Using where; Using index
 1	SIMPLE	t3	ref	PRIMARY	PRIMARY	5	test.t1.b	1	Using index
 set @show_explain_probe_select_id=1;
 set debug_dbug='+d,show_explain_probe_do_select';
@@ -1044,7 +1044,7 @@ SELECT b AS field1, b AS field2 FROM t1, t2, t3 WHERE d = b ORDER BY field1, fie
 show explain for $thr2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	1	Using filesort
-1	SIMPLE	t1	index	b	b	6	NULL	107	Using where; Using index
+1	SIMPLE	t1	range	b	b	6	NULL	107	Using where; Using index
 1	SIMPLE	t3	ref	PRIMARY	PRIMARY	5	test.t1.b	1	Using index
 Warnings:
 Note	1003	SELECT b AS field1, b AS field2 FROM t1, t2, t3 WHERE d = b ORDER BY field1, field2
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 5678a455234..55184f42c97 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -6974,7 +6974,7 @@ INSERT INTO t2 VALUES (45),(17),(20);
 EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
-2	SUBQUERY	t1	index	a	a	5	NULL	2	Using where; Using index
+2	SUBQUERY	t1	range	a	a	5	NULL	2	Using where; Using index
 2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
 DROP TABLE t1,t2;
 #
@@ -7008,7 +7008,7 @@ INSERT INTO t2 VALUES (45),(17),(20);
 EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
-2	SUBQUERY	t1	index	a	a	5	NULL	2	Using where; Using index
+2	SUBQUERY	t1	range	a	a	5	NULL	2	Using where; Using index
 2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
 DROP TABLE t1,t2;
 #
diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
index 6cc627ad16c..30b020cbcf6 100644
--- a/mysql-test/r/subselect_mat.result
+++ b/mysql-test/r/subselect_mat.result
@@ -103,7 +103,7 @@ explain extended
 select * from t1i where a1 in (select max(b1) from t2i where b1 > '0' group by b1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1i	index	NULL	#	18	#	3	100.00	#
-2	MATERIALIZED	t2i	index	it2i1,it2i3	#	9	#	5	100.00	#
+2	MATERIALIZED	t2i	range	it2i1,it2i3	#	9	#	5	100.00	#
 Warnings:
 Note	1003	select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <expr_cache><`test`.`t1i`.`a1`>(<in_optimizer>(`test`.`t1i`.`a1`,`test`.`t1i`.`a1` in ( <materialize> (select max(`test`.`t2i`.`b1`) from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1` ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key where ((`test`.`t1i`.`a1` = `<subquery2>`.`max(b1)`))))))
 select * from t1i where a1 in (select max(b1) from t2i where b1 > '0' group by b1);
diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result
index 5434bb920e3..58ee06d754d 100644
--- a/mysql-test/r/subselect_no_exists_to_in.result
+++ b/mysql-test/r/subselect_no_exists_to_in.result
@@ -6974,7 +6974,7 @@ INSERT INTO t2 VALUES (45),(17),(20);
 EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
-2	SUBQUERY	t1	index	a	a	5	NULL	2	Using where; Using index
+2	SUBQUERY	t1	range	a	a	5	NULL	2	Using where; Using index
 2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
 DROP TABLE t1,t2;
 #
@@ -7008,7 +7008,7 @@ INSERT INTO t2 VALUES (45),(17),(20);
 EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
-2	SUBQUERY	t1	index	a	a	5	NULL	2	Using where; Using index
+2	SUBQUERY	t1	range	a	a	5	NULL	2	Using where; Using index
 2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
 DROP TABLE t1,t2;
 #
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index 3325889e1e0..53307efdb89 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -6968,7 +6968,7 @@ INSERT INTO t2 VALUES (45),(17),(20);
 EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
-2	SUBQUERY	t1	index	a	a	5	NULL	2	Using where; Using index
+2	SUBQUERY	t1	range	a	a	5	NULL	2	Using where; Using index
 2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
 DROP TABLE t1,t2;
 #
@@ -7001,7 +7001,7 @@ INSERT INTO t2 VALUES (45),(17),(20);
 EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
-2	SUBQUERY	t1	index	a	a	5	NULL	2	Using where; Using index
+2	SUBQUERY	t1	range	a	a	5	NULL	2	Using where; Using index
 2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
 DROP TABLE t1,t2;
 #
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index 01af3ea4b16..c228ae46347 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -6965,7 +6965,7 @@ INSERT INTO t2 VALUES (45),(17),(20);
 EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
-2	SUBQUERY	t1	index	a	a	5	NULL	2	Using where; Using index
+2	SUBQUERY	t1	range	a	a	5	NULL	2	Using where; Using index
 2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
 DROP TABLE t1,t2;
 #
@@ -6999,7 +6999,7 @@ INSERT INTO t2 VALUES (45),(17),(20);
 EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
-2	SUBQUERY	t1	index	a	a	5	NULL	2	Using where; Using index
+2	SUBQUERY	t1	range	a	a	5	NULL	2	Using where; Using index
 2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
 DROP TABLE t1,t2;
 #
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index 16ebf7a4199..7d1dde6498e 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -6980,7 +6980,7 @@ INSERT INTO t2 VALUES (45),(17),(20);
 EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
-2	SUBQUERY	t1	index	a	a	5	NULL	2	Using where; Using index
+2	SUBQUERY	t1	range	a	a	5	NULL	2	Using where; Using index
 2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
 DROP TABLE t1,t2;
 #
@@ -7014,7 +7014,7 @@ INSERT INTO t2 VALUES (45),(17),(20);
 EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
-2	SUBQUERY	t1	index	a	a	5	NULL	2	Using where; Using index
+2	SUBQUERY	t1	range	a	a	5	NULL	2	Using where; Using index
 2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
 DROP TABLE t1,t2;
 #
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index d98f7ad67de..c850644fbaa 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -6965,7 +6965,7 @@ INSERT INTO t2 VALUES (45),(17),(20);
 EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
-2	SUBQUERY	t1	index	a	a	5	NULL	2	Using where; Using index
+2	SUBQUERY	t1	range	a	a	5	NULL	2	Using where; Using index
 2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
 DROP TABLE t1,t2;
 #
@@ -6999,7 +6999,7 @@ INSERT INTO t2 VALUES (45),(17),(20);
 EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
-2	SUBQUERY	t1	index	a	a	5	NULL	2	Using where; Using index
+2	SUBQUERY	t1	range	a	a	5	NULL	2	Using where; Using index
 2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
 DROP TABLE t1,t2;
 #
diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result
index 95dfc34777b..7417ab56ff8 100644
--- a/mysql-test/r/subselect_sj_mat.result
+++ b/mysql-test/r/subselect_sj_mat.result
@@ -107,7 +107,7 @@ select * from t1i where a1 in (select max(b1) from t2i where b1 > '0' group by b
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1i	index	it1i1,it1i3	#	18	#	3	100.00	#
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	#	8	#	1	100.00	#
-2	MATERIALIZED	t2i	index	it2i1,it2i3	#	9	#	5	100.00	#
+2	MATERIALIZED	t2i	range	it2i1,it2i3	#	9	#	5	100.00	#
 Warnings:
 Note	1003	select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from  <materialize> (select max(`test`.`t2i`.`b1`) from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`) join `test`.`t1i` where (`<subquery2>`.`max(b1)` = `test`.`t1i`.`a1`)
 select * from t1i where a1 in (select max(b1) from t2i where b1 > '0' group by b1);
diff --git a/mysql-test/t/order_by_optimizer_innodb.test b/mysql-test/t/order_by_optimizer_innodb.test
new file mode 100644
index 00000000000..90430d11549
--- /dev/null
+++ b/mysql-test/t/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;
+