Merge 10.2 into 10.3

10 files changed, 495 insertions, 652 deletions

diff --git a/mysql-test/main/fast_prefix_index_fetch_innodb.result b/mysql-test/main/fast_prefix_index_fetch_innodb.result
index c6d96389b08..ef297e5c6b5 100644
--- a/mysql-test/main/fast_prefix_index_fetch_innodb.result
+++ b/mysql-test/main/fast_prefix_index_fetch_innodb.result
@@ -1,4 +1,4 @@
-drop table if exists prefixinno;
+SET @save_opt= @@GLOBAL.innodb_prefix_index_cluster_optimization;
 set global innodb_prefix_index_cluster_optimization = ON;
 show variables like 'innodb_prefix_index_cluster_optimization';
 Variable_name	Value
@@ -346,10 +346,10 @@ f1
 🐱🌑
 select @cluster_lookups;
 @cluster_lookups
-2
+1
 select @cluster_lookups_avoided;
 @cluster_lookups_avoided
-0
+1
 # Eligible - record length is shorter than prefix length
 SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '🌑%';
 f1
@@ -366,10 +366,10 @@ f1
 🌒
 select @cluster_lookups;
 @cluster_lookups
-1
+0
 select @cluster_lookups_avoided;
 @cluster_lookups_avoided
-1
+2
 DROP TABLE t1;
 CREATE TABLE t1(
 col1 INT,
@@ -398,4 +398,60 @@ select @cluster_lookups_avoided;
 @cluster_lookups_avoided
 0
 DROP TABLE t1;
-set global innodb_prefix_index_cluster_optimization = OFF;
+#
+# MDEV-20464 Division by 0 in row_search_with_covering_prefix()
+#
+CREATE TABLE t1 (f1 INT, f2 INT AS (f1), f3 INT AS (f1), f4 INT AS (f1),
+KEY (f1,f2,f3)) ENGINE=InnoDB;
+INSERT INTO t1 (f1) VALUES (NULL),(0);
+SELECT f1, MAX(f3), COUNT(f4) FROM t1 GROUP BY f1;
+f1	MAX(f3)	COUNT(f4)
+NULL	NULL	0
+0	0	1
+DROP TABLE t1;
+#
+# MDEV-23600 Division by 0 in row_search_with_covering_prefix()
+#
+CREATE TABLE t(c POINT UNIQUE) ENGINE=InnoDB;
+INSERT t SET c=POINT(1,1);
+SELECT * FROM t WHERE c > (SELECT MAX(c) FROM t);
+c
+DROP TABLE t;
+#
+# MDEV-12486 Wrong results with innodb_prefix_index_cluster_optimization
+#
+CREATE TABLE wp_blogs (
+blog_id bigint(20) NOT NULL auto_increment,
+site_id bigint(20) NOT NULL default '0',
+domain varchar(200) NOT NULL default '',
+path varchar(100) NOT NULL default '',
+registered datetime NOT NULL default '0000-00-00 00:00:00',
+last_updated datetime NOT NULL default '0000-00-00 00:00:00',
+public tinyint(2) NOT NULL default '1',
+archived tinyint(2) NOT NULL default '0',
+mature tinyint(2) NOT NULL default '0',
+spam tinyint(2) NOT NULL default '0',
+deleted tinyint(2) NOT NULL default '0',
+lang_id int(11) NOT NULL default '0',
+PRIMARY KEY  (blog_id),
+KEY domain (domain(50),path(5)),
+KEY lang_id (lang_id)
+) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
+INSERT INTO wp_blogs (domain, path) VALUES
+('domain.no', '/fondsinvesteringer/'), ('domain.no', '/'),
+('foo', 'bar'), ('bar', 'foo'), ('foo', 'foo'), ('bar', 'bar'),
+('foo', 'foobar'), ('bar', 'foobar'), ('foobar', 'foobar');
+SET GLOBAL innodb_prefix_index_cluster_optimization=off;
+SELECT blog_id FROM wp_blogs WHERE domain IN ('domain.no')
+AND path IN ( '/fondsinvesteringer/', '/' );
+blog_id
+2
+1
+SET GLOBAL innodb_prefix_index_cluster_optimization=on;
+SELECT blog_id FROM wp_blogs WHERE domain IN ('domain.no')
+AND path IN ( '/fondsinvesteringer/', '/' );
+blog_id
+2
+1
+DROP TABLE wp_blogs;
+SET GLOBAL innodb_prefix_index_cluster_optimization = @save_opt;
diff --git a/mysql-test/main/fast_prefix_index_fetch_innodb.test b/mysql-test/main/fast_prefix_index_fetch_innodb.test
index c3b3440d82d..1987416ff87 100644
--- a/mysql-test/main/fast_prefix_index_fetch_innodb.test
+++ b/mysql-test/main/fast_prefix_index_fetch_innodb.test
@@ -1,9 +1,6 @@
 -- source include/have_innodb.inc
 
---disable_warnings
-drop table if exists prefixinno;
---enable_warnings
-
+SET @save_opt= @@GLOBAL.innodb_prefix_index_cluster_optimization;
 set global innodb_prefix_index_cluster_optimization = ON;
 show variables like 'innodb_prefix_index_cluster_optimization';
 
@@ -665,4 +662,58 @@ select @cluster_lookups;
 select @cluster_lookups_avoided;
 
 DROP TABLE t1;
-set global innodb_prefix_index_cluster_optimization = OFF;
+
+--echo #
+--echo # MDEV-20464 Division by 0 in row_search_with_covering_prefix()
+--echo #
+CREATE TABLE t1 (f1 INT, f2 INT AS (f1), f3 INT AS (f1), f4 INT AS (f1),
+                 KEY (f1,f2,f3)) ENGINE=InnoDB;
+INSERT INTO t1 (f1) VALUES (NULL),(0);
+SELECT f1, MAX(f3), COUNT(f4) FROM t1 GROUP BY f1;
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-23600 Division by 0 in row_search_with_covering_prefix()
+--echo #
+CREATE TABLE t(c POINT UNIQUE) ENGINE=InnoDB;
+INSERT t SET c=POINT(1,1);
+SELECT * FROM t WHERE c > (SELECT MAX(c) FROM t);
+DROP TABLE t;
+
+--echo #
+--echo # MDEV-12486 Wrong results with innodb_prefix_index_cluster_optimization
+--echo #
+CREATE TABLE wp_blogs (
+  blog_id bigint(20) NOT NULL auto_increment,
+  site_id bigint(20) NOT NULL default '0',
+  domain varchar(200) NOT NULL default '',
+  path varchar(100) NOT NULL default '',
+  registered datetime NOT NULL default '0000-00-00 00:00:00',
+  last_updated datetime NOT NULL default '0000-00-00 00:00:00',
+  public tinyint(2) NOT NULL default '1',
+  archived tinyint(2) NOT NULL default '0',
+  mature tinyint(2) NOT NULL default '0',
+  spam tinyint(2) NOT NULL default '0',
+  deleted tinyint(2) NOT NULL default '0',
+  lang_id int(11) NOT NULL default '0',
+  PRIMARY KEY  (blog_id),
+  KEY domain (domain(50),path(5)),
+  KEY lang_id (lang_id)
+) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
+
+INSERT INTO wp_blogs (domain, path) VALUES
+('domain.no', '/fondsinvesteringer/'), ('domain.no', '/'),
+('foo', 'bar'), ('bar', 'foo'), ('foo', 'foo'), ('bar', 'bar'),
+('foo', 'foobar'), ('bar', 'foobar'), ('foobar', 'foobar');
+
+SET GLOBAL innodb_prefix_index_cluster_optimization=off;
+SELECT blog_id FROM wp_blogs WHERE domain IN ('domain.no')
+AND path IN ( '/fondsinvesteringer/', '/' );
+
+SET GLOBAL innodb_prefix_index_cluster_optimization=on;
+SELECT blog_id FROM wp_blogs WHERE domain IN ('domain.no')
+AND path IN ( '/fondsinvesteringer/', '/' );
+
+DROP TABLE wp_blogs;
+
+SET GLOBAL innodb_prefix_index_cluster_optimization = @save_opt;
diff --git a/mysql-test/main/innodb_ext_key,off.rdiff b/mysql-test/main/innodb_ext_key,off.rdiff
new file mode 100644
index 00000000000..47ca1e6849a
--- /dev/null
+++ b/mysql-test/main/innodb_ext_key,off.rdiff
@@ -0,0 +1,295 @@
+@@ -4,7 +4,7 @@
+ explain
+ select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01';
+ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+-1	SIMPLE	lineitem	ref	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity	i_l_shipdate	8	const,const	1	Using index
++1	SIMPLE	lineitem	ref	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity	PRIMARY	4	const	5	Using where
+ flush status;
+ select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01';
+ count(*)
+@@ -14,7 +14,7 @@
+ Handler_read_first	0
+ Handler_read_key	1
+ Handler_read_last	0
+-Handler_read_next	1
++Handler_read_next	5
+ Handler_read_prev	0
+ Handler_read_retry	0
+ Handler_read_rnd	0
+@@ -45,7 +45,7 @@
+ select count(*) from lineitem
+ where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000;
+ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+-1	SIMPLE	lineitem	range	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity	i_l_shipdate	8	NULL	1	Using where; Using index
++1	SIMPLE	lineitem	ref	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity	i_l_shipdate	4	const	6	Using where; Using index
+ flush status;
+ select count(*) from lineitem
+ where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000;
+@@ -56,7 +56,7 @@
+ Handler_read_first	0
+ Handler_read_key	1
+ Handler_read_last	0
+-Handler_read_next	1
++Handler_read_next	6
+ Handler_read_prev	0
+ Handler_read_retry	0
+ Handler_read_rnd	0
+@@ -66,7 +66,7 @@
+ select l_orderkey, l_linenumber from lineitem
+ where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
+ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+-1	SIMPLE	lineitem	range	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity	i_l_shipdate	8	NULL	3	Using where; Using index
++1	SIMPLE	lineitem	ref	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity	i_l_shipdate	4	const	6	Using where; Using index
+ flush status;
+ select l_orderkey, l_linenumber from lineitem
+ where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
+@@ -79,7 +79,7 @@
+ Handler_read_first	0
+ Handler_read_key	1
+ Handler_read_last	0
+-Handler_read_next	3
++Handler_read_next	6
+ Handler_read_prev	0
+ Handler_read_retry	0
+ Handler_read_rnd	0
+@@ -88,7 +88,7 @@
+ explain
+ select min(l_orderkey) from lineitem where l_shipdate='1992-07-01';
+ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+-1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
++1	SIMPLE	lineitem	ref	i_l_shipdate	i_l_shipdate	4	const	6	Using index
+ flush status;
+ select min(l_orderkey) from lineitem where l_shipdate='1992-07-01';
+ min(l_orderkey)
+@@ -98,7 +98,7 @@
+ Handler_read_first	0
+ Handler_read_key	1
+ Handler_read_last	0
+-Handler_read_next	0
++Handler_read_next	6
+ Handler_read_prev	0
+ Handler_read_retry	0
+ Handler_read_rnd	0
+@@ -108,7 +108,7 @@
+ select min(l_orderkey) from lineitem 
+ where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
+ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+-1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
++1	SIMPLE	lineitem	ref	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity	i_l_shipdate	4	const	6	Using where; Using index
+ flush status;
+ select min(l_orderkey) from lineitem 
+ where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
+@@ -119,7 +119,7 @@
+ Handler_read_first	0
+ Handler_read_key	1
+ Handler_read_last	0
+-Handler_read_next	0
++Handler_read_next	6
+ Handler_read_prev	0
+ Handler_read_retry	0
+ Handler_read_rnd	0
+@@ -129,7 +129,7 @@
+ select max(l_linenumber) from lineitem 
+ where l_shipdate='1992-07-01' and l_orderkey=130;
+ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+-1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
++1	SIMPLE	lineitem	ref	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity	PRIMARY	4	const	5	Using where
+ flush status;
+ select max(l_linenumber) from lineitem 
+ where l_shipdate='1992-07-01' and l_orderkey=130;
+@@ -140,7 +140,7 @@
+ Handler_read_first	0
+ Handler_read_key	1
+ Handler_read_last	0
+-Handler_read_next	0
++Handler_read_next	5
+ Handler_read_prev	0
+ Handler_read_retry	0
+ Handler_read_rnd	0
+@@ -152,7 +152,7 @@
+ where l_shipdate='1992-07-01' and l_orderkey=130 
+ or l_receiptdate='1992-07-01' and l_orderkey=5603;
+ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+-1	SIMPLE	lineitem	index_merge	i_l_shipdate,i_l_receiptdate	i_l_shipdate,i_l_receiptdate	8,8	NULL	2	Using union(i_l_shipdate,i_l_receiptdate); Using where
++1	SIMPLE	lineitem	index_merge	i_l_shipdate,i_l_receiptdate	i_l_shipdate,i_l_receiptdate	4,4	NULL	9	Using union(i_l_shipdate,i_l_receiptdate); Using where
+ flush status;
+ select l_orderkey, l_linenumber 
+ from lineitem use index (i_l_shipdate, i_l_receiptdate)
+@@ -166,10 +166,10 @@
+ Handler_read_first	0
+ Handler_read_key	2
+ Handler_read_last	0
+-Handler_read_next	2
++Handler_read_next	9
+ Handler_read_prev	0
+ Handler_read_retry	0
+-Handler_read_rnd	2
++Handler_read_rnd	9
+ Handler_read_rnd_deleted	0
+ Handler_read_rnd_next	0
+ explain
+@@ -178,7 +178,7 @@
+ where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000
+ or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000;
+ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+-1	SIMPLE	lineitem	index_merge	i_l_shipdate,i_l_receiptdate	i_l_shipdate,i_l_receiptdate	8,8	NULL	3	Using sort_union(i_l_shipdate,i_l_receiptdate); Using where
++1	SIMPLE	lineitem	index_merge	i_l_shipdate,i_l_receiptdate	i_l_shipdate,i_l_receiptdate	4,4	NULL	9	Using union(i_l_shipdate,i_l_receiptdate); Using where
+ flush status;
+ select l_orderkey, l_linenumber
+ from lineitem use index (i_l_shipdate, i_l_receiptdate)
+@@ -193,10 +193,10 @@
+ Handler_read_first	0
+ Handler_read_key	2
+ Handler_read_last	0
+-Handler_read_next	3
++Handler_read_next	9
+ Handler_read_prev	0
+ Handler_read_retry	0
+-Handler_read_rnd	3
++Handler_read_rnd	9
+ Handler_read_rnd_deleted	0
+ Handler_read_rnd_next	0
+ explain
+@@ -204,7 +204,7 @@
+ where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000
+ or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000;
+ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+-1	SIMPLE	lineitem	index_merge	PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity	i_l_shipdate,i_l_receiptdate	8,8	NULL	3	Using sort_union(i_l_shipdate,i_l_receiptdate); Using where
++1	SIMPLE	lineitem	index_merge	PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity	i_l_shipdate,PRIMARY,i_l_receiptdate,PRIMARY	4,4,4,4	NULL	2	Using union(intersect(i_l_shipdate,PRIMARY),intersect(i_l_receiptdate,PRIMARY)); Using where
+ flush status;
+ select l_orderkey, l_linenumber from lineitem 
+ where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000
+@@ -218,7 +218,7 @@
+ Handler_read_first	0
+ Handler_read_key	2
+ Handler_read_last	0
+-Handler_read_next	3
++Handler_read_next	9
+ Handler_read_prev	0
+ Handler_read_retry	0
+ Handler_read_rnd	3
+@@ -228,7 +228,7 @@
+ select max(l_orderkey) from lineitem
+ where l_partkey between 1 and 10 group by l_partkey;
+ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+-1	SIMPLE	lineitem	range	i_l_suppkey_partkey,i_l_partkey	i_l_partkey	5	NULL	#	Using where; Using index for group-by
++1	SIMPLE	lineitem	range	i_l_suppkey_partkey,i_l_partkey	i_l_partkey	5	NULL	#	Using where; Using index
+ flush status;
+ select max(l_orderkey) from lineitem
+ where l_partkey between 1 and 10 group by l_partkey;
+@@ -246,9 +246,9 @@
+ show status like 'handler_read%';
+ Variable_name	Value
+ Handler_read_first	0
+-Handler_read_key	21
+-Handler_read_last	1
+-Handler_read_next	0
++Handler_read_key	1
++Handler_read_last	0
++Handler_read_next	294
+ Handler_read_prev	0
+ Handler_read_retry	0
+ Handler_read_rnd	0
+@@ -258,7 +258,7 @@
+ select max(l_orderkey) from lineitem
+ where l_suppkey in (1,4) group by l_suppkey;
+ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+-1	SIMPLE	lineitem	range	i_l_suppkey	i_l_suppkey	5	NULL	#	Using where; Using index for group-by
++1	SIMPLE	lineitem	range	i_l_suppkey	i_l_suppkey	5	NULL	#	Using where; Using index
+ flush status;
+ select max(l_orderkey) from lineitem
+ where l_suppkey in (1,4) group by l_suppkey;
+@@ -268,9 +268,9 @@
+ show status like 'handler_read%';
+ Variable_name	Value
+ Handler_read_first	0
+-Handler_read_key	6
+-Handler_read_last	1
+-Handler_read_next	0
++Handler_read_key	2
++Handler_read_last	0
++Handler_read_next	1230
+ Handler_read_prev	0
+ Handler_read_retry	0
+ Handler_read_rnd	0
+@@ -286,7 +286,7 @@
+ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+ 1	SIMPLE	part	range	i_p_retailprice	i_p_retailprice	9	NULL	#	Using where; Using index
+ 1	SIMPLE	orders	ref	PRIMARY,i_o_orderdate	i_o_orderdate	4	const	#	Using index
+-1	SIMPLE	lineitem	ref	i_l_partkey	i_l_partkey	9	dbt3_s001.part.p_partkey,dbt3_s001.orders.o_orderkey	#	Using index
++1	SIMPLE	lineitem	ref	i_l_partkey	i_l_partkey	5	dbt3_s001.part.p_partkey	#	Using where; Using index
+ flush status;
+ select o_orderkey, p_partkey
+ from part use index (i_p_retailprice),
+@@ -300,7 +300,7 @@
+ Handler_read_first	0
+ Handler_read_key	3
+ Handler_read_last	0
+-Handler_read_next	3
++Handler_read_next	26
+ Handler_read_prev	0
+ Handler_read_retry	0
+ Handler_read_rnd	0
+@@ -317,8 +317,8 @@
+ select * from t0, part ignore index (primary)
+ where p_partkey=t0.a and p_size=1;
+ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+-1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	5	Using where
+-1	SIMPLE	part	eq_ref	i_p_size	i_p_size	9	const,dbt3_s001.t0.a	1	
++1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	5	
++1	SIMPLE	part	ref	i_p_size	i_p_size	5	const	5	Using index condition
+ select * from t0,  part ignore index (primary)
+ where p_partkey=t0.a and p_size=1;
+ a	p_partkey	p_name	p_mfgr	p_brand	p_type	p_size	p_container	p_retailprice	p_comment
+@@ -495,7 +495,7 @@
+ select * from t1, t3 where t3.col1=t1.a and  t3.col2=t1.a and t3.pk1=t1.a;
+ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+ 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	#	Using where
+-1	SIMPLE	t3	ref	PRIMARY,col1	col1	12	test.t1.a,test.t1.a,test.t1.a	#	Using index
++1	SIMPLE	t3	ref	PRIMARY,col1	col1	8	test.t1.a,test.t1.a	#	Using where; Using index
+ drop table t1,t2,t3;
+ #
+ # Bug mdev-4340: performance regression with extended_keys=on   
+@@ -705,13 +705,13 @@
+ select * from t1 force index(index_date_updated) 
+ where index_date_updated= 10 and index_id < 800;
+ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+-1	SIMPLE	t1	range	index_date_updated	index_date_updated	13	NULL	#	Using index condition
++1	SIMPLE	t1	ref	index_date_updated	index_date_updated	5	const	#	Using index condition
+ # This used to work from the start:
+ explain 
+ select * from t2 force index(index_date_updated) 
+ where index_date_updated= 10 and index_id < 800;
+ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+-1	SIMPLE	t2	range	index_date_updated	index_date_updated	13	NULL	#	Using index condition
++1	SIMPLE	t2	ref	index_date_updated	index_date_updated	5	const	#	Using index condition
+ drop table t0,t1,t2;
+ #
+ # MDEV-11196: Error:Run-Time Check Failure #2 - Stack around the variable 'key_buff'
+@@ -746,11 +746,12 @@
+     "select_id": 1,
+     "table": {
+       "table_name": "t1",
+-      "access_type": "range",
++      "access_type": "ref",
+       "possible_keys": ["f2"],
+       "key": "f2",
+-      "key_length": "3070",
+-      "used_key_parts": ["f2", "pk1"],
++      "key_length": "3066",
++      "used_key_parts": ["f2"],
++      "ref": ["const"],
+       "rows": 1,
+       "filtered": 100,
+       "index_condition": "t1.pk1 <= 5 and t1.pk2 <= 5 and t1.f2 = 'abc'",
+@@ -779,8 +780,8 @@
+       "access_type": "range",
+       "possible_keys": ["k1"],
+       "key": "k1",
+-      "key_length": "3011",
+-      "used_key_parts": ["pk1", "f2", "pk2"],
++      "key_length": "3007",
++      "used_key_parts": ["pk1", "f2"],
+       "rows": 1,
+       "filtered": 100,
+       "index_condition": "t1.f2 <= 5 and t1.pk2 <= 5 and t1.pk1 = 'abc'",
diff --git a/mysql-test/main/innodb_ext_key.combinations b/mysql-test/main/innodb_ext_key.combinations
new file mode 100644
index 00000000000..6ad97eaf75b
--- /dev/null
+++ b/mysql-test/main/innodb_ext_key.combinations
@@ -0,0 +1,5 @@
+[on]
+optimizer_switch=extended_keys=on
+
+[off]
+optimizer_switch=extended_keys=off
diff --git a/mysql-test/main/innodb_ext_key.result b/mysql-test/main/innodb_ext_key.result
index dc8098d9bff..a6bf31d1f35 100644
--- a/mysql-test/main/innodb_ext_key.result
+++ b/mysql-test/main/innodb_ext_key.result
@@ -1,30 +1,6 @@
-DROP TABLE IF EXISTS t1,t2,t3,t4;
-DROP DATABASE IF EXISTS dbt3_s001;
 SET SESSION STORAGE_ENGINE='InnoDB';
 CREATE DATABASE dbt3_s001;
 use dbt3_s001;
-set @save_ext_key_optimizer_switch=@@optimizer_switch;
-set optimizer_switch='extended_keys=off';
-explain
-select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01';
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	lineitem	ref	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity	PRIMARY	4	const	5	Using where
-flush status;
-select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01';
-count(*)
-1
-show status like 'handler_read%';
-Variable_name	Value
-Handler_read_first	0
-Handler_read_key	1
-Handler_read_last	0
-Handler_read_next	5
-Handler_read_prev	0
-Handler_read_retry	0
-Handler_read_rnd	0
-Handler_read_rnd_deleted	0
-Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=on';
 explain
 select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -44,29 +20,6 @@ Handler_read_retry	0
 Handler_read_rnd	0
 Handler_read_rnd_deleted	0
 Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=off';
-explain
-select count(*) from lineitem 
-where l_orderkey=130 and l_linenumber=2 and l_shipdate='1992-07-01';
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	lineitem	const	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity	PRIMARY	8	const,const	1	
-flush status;
-select count(*) from lineitem 
-where l_orderkey=130 and l_linenumber=2 and l_shipdate='1992-07-01';
-count(*)
-1
-show status like 'handler_read%';
-Variable_name	Value
-Handler_read_first	0
-Handler_read_key	1
-Handler_read_last	0
-Handler_read_next	0
-Handler_read_prev	0
-Handler_read_retry	0
-Handler_read_rnd	0
-Handler_read_rnd_deleted	0
-Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=on';
 explain
 select count(*) from lineitem
 where l_orderkey=130 and l_linenumber=2 and l_shipdate='1992-07-01';
@@ -88,29 +41,6 @@ Handler_read_retry	0
 Handler_read_rnd	0
 Handler_read_rnd_deleted	0
 Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=off';
-explain 
-select count(*) from lineitem
-where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	lineitem	ref	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity	i_l_shipdate	4	const	6	Using where; Using index
-flush status;
-select count(*) from lineitem
-where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000;
-count(*)
-1
-show status like 'handler_read%';
-Variable_name	Value
-Handler_read_first	0
-Handler_read_key	1
-Handler_read_last	0
-Handler_read_next	6
-Handler_read_prev	0
-Handler_read_retry	0
-Handler_read_rnd	0
-Handler_read_rnd_deleted	0
-Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=on';
 explain 
 select count(*) from lineitem
 where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000;
@@ -132,31 +62,6 @@ Handler_read_retry	0
 Handler_read_rnd	0
 Handler_read_rnd_deleted	0
 Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=off';
-explain
-select l_orderkey, l_linenumber from lineitem
-where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	lineitem	ref	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity	i_l_shipdate	4	const	6	Using where; Using index
-flush status;
-select l_orderkey, l_linenumber from lineitem
-where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
-l_orderkey	l_linenumber
-1088	3
-1217	1
-1221	3
-show status like 'handler_read%';
-Variable_name	Value
-Handler_read_first	0
-Handler_read_key	1
-Handler_read_last	0
-Handler_read_next	6
-Handler_read_prev	0
-Handler_read_retry	0
-Handler_read_rnd	0
-Handler_read_rnd_deleted	0
-Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=on';
 explain
 select l_orderkey, l_linenumber from lineitem
 where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
@@ -180,27 +85,6 @@ Handler_read_retry	0
 Handler_read_rnd	0
 Handler_read_rnd_deleted	0
 Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=off';
-explain
-select min(l_orderkey) from lineitem where l_shipdate='1992-07-01';
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	lineitem	ref	i_l_shipdate	i_l_shipdate	4	const	6	Using index
-flush status;
-select min(l_orderkey) from lineitem where l_shipdate='1992-07-01';
-min(l_orderkey)
-130
-show status like 'handler_read%';
-Variable_name	Value
-Handler_read_first	0
-Handler_read_key	1
-Handler_read_last	0
-Handler_read_next	6
-Handler_read_prev	0
-Handler_read_retry	0
-Handler_read_rnd	0
-Handler_read_rnd_deleted	0
-Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=on';
 explain
 select min(l_orderkey) from lineitem where l_shipdate='1992-07-01';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -220,29 +104,6 @@ Handler_read_retry	0
 Handler_read_rnd	0
 Handler_read_rnd_deleted	0
 Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=off';
-explain
-select min(l_orderkey) from lineitem 
-where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	lineitem	ref	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity	i_l_shipdate	4	const	6	Using where; Using index
-flush status;
-select min(l_orderkey) from lineitem 
-where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
-min(l_orderkey)
-1088
-show status like 'handler_read%';
-Variable_name	Value
-Handler_read_first	0
-Handler_read_key	1
-Handler_read_last	0
-Handler_read_next	6
-Handler_read_prev	0
-Handler_read_retry	0
-Handler_read_rnd	0
-Handler_read_rnd_deleted	0
-Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=on';
 explain
 select min(l_orderkey) from lineitem 
 where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
@@ -264,29 +125,6 @@ Handler_read_retry	0
 Handler_read_rnd	0
 Handler_read_rnd_deleted	0
 Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=off';
-explain
-select max(l_linenumber) from lineitem 
-where l_shipdate='1992-07-01' and l_orderkey=130;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	lineitem	ref	PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity	PRIMARY	4	const	5	Using where
-flush status;
-select max(l_linenumber) from lineitem 
-where l_shipdate='1992-07-01' and l_orderkey=130;
-max(l_linenumber)
-2
-show status like 'handler_read%';
-Variable_name	Value
-Handler_read_first	0
-Handler_read_key	1
-Handler_read_last	0
-Handler_read_next	5
-Handler_read_prev	0
-Handler_read_retry	0
-Handler_read_rnd	0
-Handler_read_rnd_deleted	0
-Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=on';
 explain
 select max(l_linenumber) from lineitem 
 where l_shipdate='1992-07-01' and l_orderkey=130;
@@ -308,34 +146,6 @@ Handler_read_retry	0
 Handler_read_rnd	0
 Handler_read_rnd_deleted	0
 Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=off';
-explain
-select l_orderkey, l_linenumber 
-from lineitem use index (i_l_shipdate, i_l_receiptdate)
-where l_shipdate='1992-07-01' and l_orderkey=130 
-or l_receiptdate='1992-07-01' and l_orderkey=5603;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	lineitem	index_merge	i_l_shipdate,i_l_receiptdate	i_l_shipdate,i_l_receiptdate	4,4	NULL	9	Using union(i_l_shipdate,i_l_receiptdate); Using where
-flush status;
-select l_orderkey, l_linenumber 
-from lineitem use index (i_l_shipdate, i_l_receiptdate)
-where l_shipdate='1992-07-01' and l_orderkey=130 
-or l_receiptdate='1992-07-01' and l_orderkey=5603;
-l_orderkey	l_linenumber
-130	2
-5603	2
-show status like 'handler_read%';
-Variable_name	Value
-Handler_read_first	0
-Handler_read_key	2
-Handler_read_last	0
-Handler_read_next	9
-Handler_read_prev	0
-Handler_read_retry	0
-Handler_read_rnd	9
-Handler_read_rnd_deleted	0
-Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=on';
 explain
 select l_orderkey, l_linenumber 
 from lineitem use index (i_l_shipdate, i_l_receiptdate)
@@ -362,35 +172,6 @@ Handler_read_retry	0
 Handler_read_rnd	2
 Handler_read_rnd_deleted	0
 Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=off';
-explain
-select l_orderkey, l_linenumber
-from lineitem use index (i_l_shipdate, i_l_receiptdate)
-where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000
-or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	lineitem	index_merge	i_l_shipdate,i_l_receiptdate	i_l_shipdate,i_l_receiptdate	4,4	NULL	9	Using union(i_l_shipdate,i_l_receiptdate); Using where
-flush status;
-select l_orderkey, l_linenumber
-from lineitem use index (i_l_shipdate, i_l_receiptdate)
-where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000
-or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000;
-l_orderkey	l_linenumber
-130	2
-5603	2
-5959	3
-show status like 'handler_read%';
-Variable_name	Value
-Handler_read_first	0
-Handler_read_key	2
-Handler_read_last	0
-Handler_read_next	9
-Handler_read_prev	0
-Handler_read_retry	0
-Handler_read_rnd	9
-Handler_read_rnd_deleted	0
-Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=on';
 explain
 select l_orderkey, l_linenumber
 from lineitem use index (i_l_shipdate, i_l_receiptdate)
@@ -418,33 +199,6 @@ Handler_read_retry	0
 Handler_read_rnd	3
 Handler_read_rnd_deleted	0
 Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=off';
-explain
-select l_orderkey, l_linenumber from lineitem 
-where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000
-or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	lineitem	index_merge	PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity	i_l_shipdate,PRIMARY,i_l_receiptdate,PRIMARY	4,4,4,4	NULL	2	Using union(intersect(i_l_shipdate,PRIMARY),intersect(i_l_receiptdate,PRIMARY)); Using where
-flush status;
-select l_orderkey, l_linenumber from lineitem 
-where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000
-or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000;
-l_orderkey	l_linenumber
-130	2
-5603	2
-5959	3
-show status like 'handler_read%';
-Variable_name	Value
-Handler_read_first	0
-Handler_read_key	2
-Handler_read_last	0
-Handler_read_next	9
-Handler_read_prev	0
-Handler_read_retry	0
-Handler_read_rnd	3
-Handler_read_rnd_deleted	0
-Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=on';
 explain
 select l_orderkey, l_linenumber from lineitem 
 where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000
@@ -470,38 +224,6 @@ Handler_read_retry	0
 Handler_read_rnd	3
 Handler_read_rnd_deleted	0
 Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=off';
-explain 
-select max(l_orderkey) from lineitem
-where l_partkey between 1 and 10 group by l_partkey;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	lineitem	range	i_l_suppkey_partkey,i_l_partkey	i_l_partkey	5	NULL	#	Using where; Using index
-flush status;
-select max(l_orderkey) from lineitem
-where l_partkey between 1 and 10 group by l_partkey;
-max(l_orderkey)
-5984
-5957
-5892
-5856
-5959
-5957
-5794
-5894
-5859
-5632
-show status like 'handler_read%';
-Variable_name	Value
-Handler_read_first	0
-Handler_read_key	1
-Handler_read_last	0
-Handler_read_next	294
-Handler_read_prev	0
-Handler_read_retry	0
-Handler_read_rnd	0
-Handler_read_rnd_deleted	0
-Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=on';
 explain 
 select max(l_orderkey) from lineitem
 where l_partkey between 1 and 10 group by l_partkey;
@@ -532,30 +254,6 @@ Handler_read_retry	0
 Handler_read_rnd	0
 Handler_read_rnd_deleted	0
 Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=off';
-explain 
-select max(l_orderkey) from lineitem
-where l_suppkey in (1,4) group by l_suppkey;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	lineitem	range	i_l_suppkey	i_l_suppkey	5	NULL	#	Using where; Using index
-flush status;
-select max(l_orderkey) from lineitem
-where l_suppkey in (1,4) group by l_suppkey;
-max(l_orderkey)
-5988
-5984
-show status like 'handler_read%';
-Variable_name	Value
-Handler_read_first	0
-Handler_read_key	2
-Handler_read_last	0
-Handler_read_next	1230
-Handler_read_prev	0
-Handler_read_retry	0
-Handler_read_rnd	0
-Handler_read_rnd_deleted	0
-Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=on';
 explain 
 select max(l_orderkey) from lineitem
 where l_suppkey in (1,4) group by l_suppkey;
@@ -579,37 +277,6 @@ Handler_read_rnd	0
 Handler_read_rnd_deleted	0
 Handler_read_rnd_next	0
 create index i_p_retailprice on part(p_retailprice);
-set optimizer_switch='extended_keys=off';
-explain
-select o_orderkey, p_partkey
-from part use index (i_p_retailprice),
-lineitem use index (i_l_partkey), orders
-where p_retailprice > 1100 and o_orderdate='1997-01-01' 
-and o_orderkey=l_orderkey and p_partkey=l_partkey;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	part	range	i_p_retailprice	i_p_retailprice	9	NULL	#	Using where; Using index
-1	SIMPLE	orders	ref	PRIMARY,i_o_orderdate	i_o_orderdate	4	const	#	Using index
-1	SIMPLE	lineitem	ref	i_l_partkey	i_l_partkey	5	dbt3_s001.part.p_partkey	#	Using where; Using index
-flush status;
-select o_orderkey, p_partkey
-from part use index (i_p_retailprice),
-lineitem use index (i_l_partkey), orders
-where p_retailprice > 1100 and o_orderdate='1997-01-01' 
-and o_orderkey=l_orderkey and p_partkey=l_partkey;
-o_orderkey	p_partkey
-5895	200
-show status like 'handler_read%';
-Variable_name	Value
-Handler_read_first	0
-Handler_read_key	3
-Handler_read_last	0
-Handler_read_next	26
-Handler_read_prev	0
-Handler_read_retry	0
-Handler_read_rnd	0
-Handler_read_rnd_deleted	0
-Handler_read_rnd_next	0
-set optimizer_switch='extended_keys=on';
 explain
 select o_orderkey, p_partkey
 from part use index (i_p_retailprice),
@@ -646,7 +313,6 @@ Handler_read_rnd_next	0
 create table t0 (a int);
 insert into t0 values (1), (2), (3), (4), (5);
 create index i_p_size on part(p_size);
-set optimizer_switch='extended_keys=on';
 explain
 select * from t0, part ignore index (primary)
 where p_partkey=t0.a and p_size=1;
@@ -667,7 +333,6 @@ use test;
 #
 set @save_optimizer_switch=@@optimizer_switch;
 SET optimizer_switch='materialization=on,semijoin=on';
-SET optimizer_switch='extended_keys=on';
 CREATE TABLE t1 (a int, b int) ENGINE=MyISAM;
 INSERT INTO t1 VALUES (1,1), (2,2);
 SELECT * FROM t1 WHERE 2 IN (SELECT MAX(s1.a) FROM t1 AS s1, t1 AS s2);
@@ -688,9 +353,7 @@ set optimizer_switch=@save_optimizer_switch;
 #                 + extended_keys = on
 # (valgrinf complains fixed by the patch for bug #914560)
 #
-set @save_optimizer_switch=@@optimizer_switch;
 SET optimizer_switch = 'derived_with_keys=on';
-SET optimizer_switch = 'extended_keys=on';
 CREATE TABLE t1 (a varchar(1)) ENGINE=MyISAM;
 INSERT INTO t1 VALUES ('j'), ('v');
 CREATE TABLE t2 (b varchar(1)) ENGINE=MyISAM;
@@ -720,21 +383,6 @@ c int NOT NULL PRIMARY KEY
 INSERT INTO t2 VALUES
 (10), (11), (12), (13), (14), 
 (15), (16), (17), (18), (19), (24);
-set @save_optimizer_switch=@@optimizer_switch;
-SET optimizer_switch = 'extended_keys=off';
-EXPLAIN
-SELECT a FROM t1 AS t, t2 
-WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b);
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t	index	a,b	b	7	NULL	10	Using index
-1	PRIMARY	t1	ref	b	b	3	test.t.b	2	Using index; Start temporary
-1	PRIMARY	t2	index	NULL	PRIMARY	4	NULL	11	Using index; End temporary; Using join buffer (flat, BNL join)
-1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t.a	1	Using index
-SELECT a FROM t1 AS t, t2 
-WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b);
-a
-24
-SET optimizer_switch = 'extended_keys=on';
 EXPLAIN
 SELECT a FROM t1 AS t, t2 
 WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b);
@@ -747,7 +395,6 @@ SELECT a FROM t1 AS t, t2
 WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b);
 a
 24
-set optimizer_switch=@save_optimizer_switch;
 DROP TABLE t1,t2;
 #
 # LP Bug #923236: hash join + extended_keys = on 
@@ -756,12 +403,10 @@ CREATE TABLE t1 (a int) ENGINE=MyISAM;
 CREATE TABLE t2 (b int) ENGINE=MyISAM;
 INSERT INTO t1 (a) VALUES (4), (6);
 INSERT INTO t2 (b) VALUES (0), (8);
-set @save_optimizer_switch=@@optimizer_switch;
 set @save_join_cache_level=@@join_cache_level;
 SET join_cache_level=3;
 SET optimizer_switch='join_cache_hashed=on';
 SET optimizer_switch='join_cache_bka=on';
-SET optimizer_switch='extended_keys=on';
 EXPLAIN
 SELECT * FROM t1, t2 WHERE b=a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -786,26 +431,16 @@ UNIQUE KEY uq (c2,c3),
 KEY c3 (c3),
 KEY c4 (c4)
 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-set @save_optimizer_switch=@@optimizer_switch;
-set session optimizer_switch='extended_keys=off';
-INSERT INTO t1 (c2, c3, c4) VALUES (58291525, 2580, 'foobar')
-ON DUPLICATE KEY UPDATE c4 = VALUES(c4);
 INSERT INTO t1 (c2, c3, c4) VALUES (58291525, 2580, 'foobar')
 ON DUPLICATE KEY UPDATE c4 = VALUES(c4);
-DELETE FROM t1;
-set session optimizer_switch='extended_keys=on';
 INSERT INTO t1 (c2, c3, c4) VALUES (58291525, 2580, 'foobar')
 ON DUPLICATE KEY UPDATE c4 = VALUES(c4);
-INSERT INTO t1 (c2, c3, c4) VALUES (58291525, 2580, 'foobar')
-ON DUPLICATE KEY UPDATE c4 = VALUES(c4);
-set optimizer_switch=@save_optimizer_switch;
 DROP TABLE t1;
 #
 # Bug mdev-4220: using ref instead of eq_ref
 #                with extended_keys=on   
 # (performance regression introduced in the patch for mdev-3851)
 #
-set @save_optimizer_switch=@@optimizer_switch;
 create table t1 (a int not null) engine=innodb;
 insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 create table t2 (
@@ -815,29 +450,6 @@ insert into t2
 select 
 A.a + 10 * B.a, A.a + 10 * B.a, A.a + 10 * B.a 
 from t1 A, t1 B;
-set optimizer_switch='extended_keys=off';
-explain 
-select * from t1, t2 where t2.a=t1.a and t2.b < 2;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	
-1	SIMPLE	t2	eq_ref	a	a	4	test.t1.a	1	Using where
-flush status;
-select * from t1, t2 where t2.a=t1.a and t2.b < 2;
-a	pk	a	b
-0	0	0	0
-1	1	1	1
-show status like 'handler_read%';
-Variable_name	Value
-Handler_read_first	0
-Handler_read_key	10
-Handler_read_last	0
-Handler_read_next	0
-Handler_read_prev	0
-Handler_read_retry	0
-Handler_read_rnd	0
-Handler_read_rnd_deleted	0
-Handler_read_rnd_next	11
-set optimizer_switch='extended_keys=on';
 explain 
 select * from t1, t2 where t2.a=t1.a and t2.b < 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -874,18 +486,6 @@ analyze table t1,t3;
 Table	Op	Msg_type	Msg_text
 test.t1	analyze	status	OK
 test.t3	analyze	status	OK
-set optimizer_switch='extended_keys=off';
-explain 
-select * from t1, t3 where t3.col1=t1.a and  t3.col2=t1.a;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	#	Using where
-1	SIMPLE	t3	ref	col1	col1	8	test.t1.a,test.t1.a	#	Using index
-explain 
-select * from t1, t3 where t3.col1=t1.a and  t3.col2=t1.a and t3.pk1=t1.a;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	#	Using where
-1	SIMPLE	t3	ref	PRIMARY,col1	col1	8	test.t1.a,test.t1.a	#	Using where; Using index
-set optimizer_switch='extended_keys=on';
 explain 
 select * from t1, t3 where t3.col1=t1.a and  t3.col2=t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -900,7 +500,6 @@ drop table t1,t2,t3;
 #
 # Bug mdev-4340: performance regression with extended_keys=on   
 #
-set @save_optimizer_switch=@@optimizer_switch;
 CREATE TABLE t1 (
 page_id int(8) unsigned NOT NULL AUTO_INCREMENT,
 page_namespace int(11) NOT NULL DEFAULT '0',
@@ -1000,16 +599,6 @@ INSERT INTO t3 VALUES
 (89,'text-8008',''),(90,'text-9008',''),(91,'text-9',''),(92,'text-1009',''),
 (93,'text-2009',''),(94,'text-3009',''),(95,'text-4009',''),(96,'text-5009',''),
 (97,'text-6009',''),(98,'text-7009',''),(99,'text-8009',''),(100,'text-9009','');
-set optimizer_switch='extended_keys=off';
-EXPLAIN 
-SELECT *  FROM t1, t2 IGNORE INDEX (PRIMARY), t3 
-WHERE page_id=rev_page AND  rev_text_id=old_id  AND page_namespace=4 AND page_title='Sandbox'
-ORDER BY rev_timestamp ASC LIMIT 10;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	const	PRIMARY,name_title	name_title	261	const,const	1	
-1	SIMPLE	t2	ref	page_timestamp	page_timestamp	4	const	10	Using where
-1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.rev_text_id	1	
-set optimizer_switch='extended_keys=on';
 EXPLAIN 
 SELECT *  FROM t1, t2 IGNORE INDEX (PRIMARY), t3 
 WHERE page_id=rev_page AND  rev_text_id=old_id  AND page_namespace=4 AND page_title='Sandbox'
@@ -1027,7 +616,6 @@ create table t1 (a bigint not null unique auto_increment, b varchar(10), primary
 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;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	b	PRIMARY	8	NULL	3	Using where
@@ -1042,14 +630,6 @@ select a from t2 where b is null order by a desc limit 2;
 a
 3
 2
-set optimizer_switch='extended_keys=off';
-explain select a from t2 where b is null order by a desc limit 2;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	range	b	b	9	NULL	3	Using where; Using filesort
-select a from t2 where b is null order by a desc limit 2;
-a
-3
-2
 explain select a from t2 where b is null order by a desc;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	index	b	PRIMARY	8	NULL	3	Using where
@@ -1067,7 +647,6 @@ a
 2
 1
 drop table t1, t2;
-set optimizer_switch=@save_optimizer_switch;
 # 
 # MDEV-10325: Queries examines all rows of a tables when it should not
 # 
diff --git a/mysql-test/main/innodb_ext_key.test b/mysql-test/main/innodb_ext_key.test
index 8def57ad377..f5edd736490 100644
--- a/mysql-test/main/innodb_ext_key.test
+++ b/mysql-test/main/innodb_ext_key.test
@@ -1,9 +1,4 @@
---source include/have_innodb.inc
-
---disable_warnings
-DROP TABLE IF EXISTS t1,t2,t3,t4;
-DROP DATABASE IF EXISTS dbt3_s001;
---enable_warnings
+--source include/innodb_prefix_index_cluster_optimization.inc
 
 SET SESSION STORAGE_ENGINE='InnoDB';
 
@@ -19,32 +14,12 @@ use dbt3_s001;
 --enable_result_log
 --enable_query_log
 
-set @save_ext_key_optimizer_switch=@@optimizer_switch;
-
-set optimizer_switch='extended_keys=off';
 explain
 select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01';
 flush status;
 select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01';
 show status like 'handler_read%';
 
-set optimizer_switch='extended_keys=on';
-explain
-select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01';
-flush status;
-select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01';
-show status like 'handler_read%';
-
-set optimizer_switch='extended_keys=off';
-explain
-select count(*) from lineitem 
-  where l_orderkey=130 and l_linenumber=2 and l_shipdate='1992-07-01';
-flush status;
-select count(*) from lineitem 
-  where l_orderkey=130 and l_linenumber=2 and l_shipdate='1992-07-01';
-show status like 'handler_read%';
-
-set optimizer_switch='extended_keys=on';
 explain
 select count(*) from lineitem
   where l_orderkey=130 and l_linenumber=2 and l_shipdate='1992-07-01';
@@ -53,16 +28,6 @@ select count(*) from lineitem
   where l_orderkey=130 and l_linenumber=2 and l_shipdate='1992-07-01';
 show status like 'handler_read%';
 
-set optimizer_switch='extended_keys=off';
-explain 
-select count(*) from lineitem
-  where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000;
-flush status;
-select count(*) from lineitem
-  where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000;
-show status like 'handler_read%';
-
-set optimizer_switch='extended_keys=on';
 explain 
 select count(*) from lineitem
   where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000;
@@ -71,16 +36,6 @@ select count(*) from lineitem
   where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000;
 show status like 'handler_read%';
 
-set optimizer_switch='extended_keys=off';
-explain
-select l_orderkey, l_linenumber from lineitem
-  where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
-flush status;
-select l_orderkey, l_linenumber from lineitem
-  where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
-show status like 'handler_read%';
-
-set optimizer_switch='extended_keys=on';
 explain
 select l_orderkey, l_linenumber from lineitem
   where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
@@ -89,21 +44,12 @@ select l_orderkey, l_linenumber from lineitem
   where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
 show status like 'handler_read%';
 
-set optimizer_switch='extended_keys=off';
 explain
 select min(l_orderkey) from lineitem where l_shipdate='1992-07-01';
 flush status;
 select min(l_orderkey) from lineitem where l_shipdate='1992-07-01';
 show status like 'handler_read%';
 
-set optimizer_switch='extended_keys=on';
-explain
-select min(l_orderkey) from lineitem where l_shipdate='1992-07-01';
-flush status;
-select min(l_orderkey) from lineitem where l_shipdate='1992-07-01';
-show status like 'handler_read%';
-
-set optimizer_switch='extended_keys=off';
 explain
 select min(l_orderkey) from lineitem 
   where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
@@ -112,25 +58,6 @@ select min(l_orderkey) from lineitem
   where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
 show status like 'handler_read%';
 
-set optimizer_switch='extended_keys=on';
-explain
-select min(l_orderkey) from lineitem 
-  where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
-flush status;
-select min(l_orderkey) from lineitem 
-  where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
-show status like 'handler_read%';
-
-set optimizer_switch='extended_keys=off';
-explain
-select max(l_linenumber) from lineitem 
-  where l_shipdate='1992-07-01' and l_orderkey=130;
-flush status;
-select max(l_linenumber) from lineitem 
-  where l_shipdate='1992-07-01' and l_orderkey=130;
-show status like 'handler_read%';
-
-set optimizer_switch='extended_keys=on';
 explain
 select max(l_linenumber) from lineitem 
   where l_shipdate='1992-07-01' and l_orderkey=130;
@@ -139,7 +66,6 @@ select max(l_linenumber) from lineitem
   where l_shipdate='1992-07-01' and l_orderkey=130;
 show status like 'handler_read%';
 
-set optimizer_switch='extended_keys=off';
 explain
 select l_orderkey, l_linenumber 
   from lineitem use index (i_l_shipdate, i_l_receiptdate)
@@ -152,20 +78,6 @@ select l_orderkey, l_linenumber
         or l_receiptdate='1992-07-01' and l_orderkey=5603;
 show status like 'handler_read%';
 
-set optimizer_switch='extended_keys=on';
-explain
-select l_orderkey, l_linenumber 
-  from lineitem use index (i_l_shipdate, i_l_receiptdate)
-  where l_shipdate='1992-07-01' and l_orderkey=130 
-        or l_receiptdate='1992-07-01' and l_orderkey=5603;
-flush status;
-select l_orderkey, l_linenumber 
-  from lineitem use index (i_l_shipdate, i_l_receiptdate)
-  where l_shipdate='1992-07-01' and l_orderkey=130 
-        or l_receiptdate='1992-07-01' and l_orderkey=5603;
-show status like 'handler_read%';
-
-set optimizer_switch='extended_keys=off';
 explain
 select l_orderkey, l_linenumber
   from lineitem use index (i_l_shipdate, i_l_receiptdate)
@@ -178,20 +90,6 @@ select l_orderkey, l_linenumber
          or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000;
 show status like 'handler_read%';
 
-set optimizer_switch='extended_keys=on';
-explain
-select l_orderkey, l_linenumber
-  from lineitem use index (i_l_shipdate, i_l_receiptdate)
-  where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000
-         or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000;
-flush status;
-select l_orderkey, l_linenumber
-  from lineitem use index (i_l_shipdate, i_l_receiptdate)
-  where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000
-         or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000;
-show status like 'handler_read%';
-
-set optimizer_switch='extended_keys=off';
 explain
 select l_orderkey, l_linenumber from lineitem 
   where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000
@@ -202,28 +100,6 @@ select l_orderkey, l_linenumber from lineitem
         or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000;
 show status like 'handler_read%';
 
-set optimizer_switch='extended_keys=on';
-explain
-select l_orderkey, l_linenumber from lineitem 
-  where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000
-        or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000;
-flush status;
-select l_orderkey, l_linenumber from lineitem 
-  where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000
-        or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000;
-show status like 'handler_read%';
-
-set optimizer_switch='extended_keys=off';
---replace_column 9 #
-explain 
-select max(l_orderkey) from lineitem
-  where l_partkey between 1 and 10 group by l_partkey;
-flush status;
-select max(l_orderkey) from lineitem
-  where l_partkey between 1 and 10 group by l_partkey;
-show status like 'handler_read%';
-
-set optimizer_switch='extended_keys=on';
 --replace_column 9 #
 explain 
 select max(l_orderkey) from lineitem
@@ -233,17 +109,6 @@ select max(l_orderkey) from lineitem
   where l_partkey between 1 and 10 group by l_partkey;
 show status like 'handler_read%';
 
-set optimizer_switch='extended_keys=off';
---replace_column 9 #
-explain 
-select max(l_orderkey) from lineitem
-  where l_suppkey in (1,4) group by l_suppkey;
-flush status;
-select max(l_orderkey) from lineitem
-  where l_suppkey in (1,4) group by l_suppkey;
-show status like 'handler_read%';
-
-set optimizer_switch='extended_keys=on';
 --replace_column 9 #
 explain 
 select max(l_orderkey) from lineitem
@@ -255,23 +120,6 @@ show status like 'handler_read%';
 
 create index i_p_retailprice on part(p_retailprice);
 
-set optimizer_switch='extended_keys=off';
---replace_column 9 #
-explain
-select o_orderkey, p_partkey
-  from part use index (i_p_retailprice),
-       lineitem use index (i_l_partkey), orders
-  where p_retailprice > 1100 and o_orderdate='1997-01-01' 
-        and o_orderkey=l_orderkey and p_partkey=l_partkey;
-flush status;
-select o_orderkey, p_partkey
-  from part use index (i_p_retailprice),
-       lineitem use index (i_l_partkey), orders
-  where p_retailprice > 1100 and o_orderdate='1997-01-01' 
-        and o_orderkey=l_orderkey and p_partkey=l_partkey;
-show status like 'handler_read%';
-
-set optimizer_switch='extended_keys=on';
 --replace_column 9 #
 explain
 select o_orderkey, p_partkey
@@ -296,8 +144,6 @@ create table t0 (a int);
 insert into t0 values (1), (2), (3), (4), (5);
 create index i_p_size on part(p_size);
 
-set optimizer_switch='extended_keys=on';
-
 explain
 select * from t0, part ignore index (primary)
   where p_partkey=t0.a and p_size=1;
@@ -320,7 +166,6 @@ use test;
 set @save_optimizer_switch=@@optimizer_switch;
 
 SET optimizer_switch='materialization=on,semijoin=on';
-SET optimizer_switch='extended_keys=on';
 
 CREATE TABLE t1 (a int, b int) ENGINE=MyISAM;
 INSERT INTO t1 VALUES (1,1), (2,2);
@@ -339,10 +184,7 @@ set optimizer_switch=@save_optimizer_switch;
 --echo # (valgrinf complains fixed by the patch for bug #914560)
 --echo #
 
-set @save_optimizer_switch=@@optimizer_switch;
-
 SET optimizer_switch = 'derived_with_keys=on';
-SET optimizer_switch = 'extended_keys=on';
 
 CREATE TABLE t1 (a varchar(1)) ENGINE=MyISAM;
 INSERT INTO t1 VALUES ('j'), ('v');
@@ -382,24 +224,12 @@ INSERT INTO t2 VALUES
   (10), (11), (12), (13), (14), 
   (15), (16), (17), (18), (19), (24);
 
-set @save_optimizer_switch=@@optimizer_switch;
-
-SET optimizer_switch = 'extended_keys=off';
-EXPLAIN
-SELECT a FROM t1 AS t, t2 
-  WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b);
-SELECT a FROM t1 AS t, t2 
-  WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b);
-
-SET optimizer_switch = 'extended_keys=on';
 EXPLAIN
 SELECT a FROM t1 AS t, t2 
   WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b);
 SELECT a FROM t1 AS t, t2 
   WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b);
 
-set optimizer_switch=@save_optimizer_switch;
-
 DROP TABLE t1,t2;
 
 --echo #
@@ -413,13 +243,11 @@ CREATE TABLE t2 (b int) ENGINE=MyISAM;
 INSERT INTO t1 (a) VALUES (4), (6);
 INSERT INTO t2 (b) VALUES (0), (8);
 
-set @save_optimizer_switch=@@optimizer_switch;
 set @save_join_cache_level=@@join_cache_level;
 
 SET join_cache_level=3;
 SET optimizer_switch='join_cache_hashed=on';
 SET optimizer_switch='join_cache_bka=on';
-SET optimizer_switch='extended_keys=on';
 
 EXPLAIN
 SELECT * FROM t1, t2 WHERE b=a;
@@ -448,24 +276,11 @@ KEY c4 (c4)
 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
 
 
-set @save_optimizer_switch=@@optimizer_switch;
-
-set session optimizer_switch='extended_keys=off';
-INSERT INTO t1 (c2, c3, c4) VALUES (58291525, 2580, 'foobar')
-  ON DUPLICATE KEY UPDATE c4 = VALUES(c4);
-INSERT INTO t1 (c2, c3, c4) VALUES (58291525, 2580, 'foobar')
-  ON DUPLICATE KEY UPDATE c4 = VALUES(c4);
-
-DELETE FROM t1;
-
-set session optimizer_switch='extended_keys=on';
 INSERT INTO t1 (c2, c3, c4) VALUES (58291525, 2580, 'foobar')
   ON DUPLICATE KEY UPDATE c4 = VALUES(c4);
 INSERT INTO t1 (c2, c3, c4) VALUES (58291525, 2580, 'foobar')
   ON DUPLICATE KEY UPDATE c4 = VALUES(c4);
 
-set optimizer_switch=@save_optimizer_switch;
-
 DROP TABLE t1;
 
 --echo #
@@ -474,8 +289,6 @@ DROP TABLE t1;
 --echo # (performance regression introduced in the patch for mdev-3851)
 --echo #
 
-set @save_optimizer_switch=@@optimizer_switch;
-
 create table t1 (a int not null) engine=innodb;
 
 insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
@@ -489,14 +302,6 @@ select
   A.a + 10 * B.a, A.a + 10 * B.a, A.a + 10 * B.a 
 from t1 A, t1 B;
 
-set optimizer_switch='extended_keys=off';
-explain 
-select * from t1, t2 where t2.a=t1.a and t2.b < 2;
-flush status;
-select * from t1, t2 where t2.a=t1.a and t2.b < 2;
-show status like 'handler_read%';
-
-set optimizer_switch='extended_keys=on';
 explain 
 select * from t1, t2 where t2.a=t1.a and t2.b < 2;
 flush status;
@@ -522,15 +327,6 @@ alter table t3 add primary key (pk1, pk2);
 alter table t3 add  key (col1, col2);
 analyze table t1,t3;
 
-set optimizer_switch='extended_keys=off';
---replace_column 9 #
-explain 
-select * from t1, t3 where t3.col1=t1.a and  t3.col2=t1.a;
---replace_column 9 #
-explain 
-select * from t1, t3 where t3.col1=t1.a and  t3.col2=t1.a and t3.pk1=t1.a;
-
-set optimizer_switch='extended_keys=on';
 --replace_column 9 #
 explain 
 select * from t1, t3 where t3.col1=t1.a and  t3.col2=t1.a;
@@ -544,8 +340,6 @@ drop table t1,t2,t3;
 --echo # Bug mdev-4340: performance regression with extended_keys=on   
 --echo #
 
-set @save_optimizer_switch=@@optimizer_switch;
-
 CREATE TABLE t1 (
   page_id int(8) unsigned NOT NULL AUTO_INCREMENT,
   page_namespace int(11) NOT NULL DEFAULT '0',
@@ -649,13 +443,6 @@ INSERT INTO t3 VALUES
 (97,'text-6009',''),(98,'text-7009',''),(99,'text-8009',''),(100,'text-9009','');
 
 
-set optimizer_switch='extended_keys=off';
-EXPLAIN 
-SELECT *  FROM t1, t2 IGNORE INDEX (PRIMARY), t3 
-  WHERE page_id=rev_page AND  rev_text_id=old_id  AND page_namespace=4 AND page_title='Sandbox'
-ORDER BY rev_timestamp ASC LIMIT 10;
-
-set optimizer_switch='extended_keys=on';
 EXPLAIN 
 SELECT *  FROM t1, t2 IGNORE INDEX (PRIMARY), t3 
   WHERE page_id=rev_page AND  rev_text_id=old_id  AND page_namespace=4 AND page_title='Sandbox'
@@ -674,14 +461,10 @@ create table t2 (a bigint not null unique auto_increment, b varchar(10), primary
 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;
@@ -691,8 +474,6 @@ select a from t2 where b is null order by a desc,a,a;
 
 drop table t1, t2;
 
-set optimizer_switch=@save_optimizer_switch;
-
 --echo # 
 --echo # MDEV-10325: Queries examines all rows of a tables when it should not
 --echo # 
diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result
index 0b28f15b968..9543cdab97e 100644
--- a/mysql-test/main/order_by.result
+++ b/mysql-test/main/order_by.result
@@ -3356,6 +3356,22 @@ SET sort_buffer_size= @save_sort_buffer_size;
 SET max_length_for_sort_data= @save_max_length_for_sort_data;
 DROP TABLE t1;
 #
+# MDEV-23596: Assertion `tab->ref.use_count' failed in join_read_key_unlock_row
+#
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, KEY(b));
+INSERT INTO t1 VALUES (0, 1),(1, 2);
+CREATE TABLE t2 SELECT * FROM t1;
+EXPLAIN SELECT (SELECT 1 FROM t1 WHERE t1.a=t2.b ORDER BY t1.b LIMIT 1) AS c FROM t2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
+2	DEPENDENT SUBQUERY	t1	index	PRIMARY	b	5	NULL	1	Using where
+SELECT (SELECT 1 FROM t1 WHERE t1.a=t2.b ORDER BY t1.b LIMIT 1) AS c FROM t2;
+c
+1
+NULL
+DROP TABLE t1,t2;
+# end of 10.1 tests
+#
 # MDEV-13994: Bad join results with orderby_uses_equalities=on
 #
 CREATE TABLE books (
diff --git a/mysql-test/main/order_by.test b/mysql-test/main/order_by.test
index ee61564e6c1..e8708a87984 100644
--- a/mysql-test/main/order_by.test
+++ b/mysql-test/main/order_by.test
@@ -2201,6 +2201,21 @@ SET max_length_for_sort_data= @save_max_length_for_sort_data;
 DROP TABLE t1;
 
 --echo #
+--echo # MDEV-23596: Assertion `tab->ref.use_count' failed in join_read_key_unlock_row
+--echo #
+
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, KEY(b));
+INSERT INTO t1 VALUES (0, 1),(1, 2);
+CREATE TABLE t2 SELECT * FROM t1;
+
+EXPLAIN SELECT (SELECT 1 FROM t1 WHERE t1.a=t2.b ORDER BY t1.b LIMIT 1) AS c FROM t2;
+SELECT (SELECT 1 FROM t1 WHERE t1.a=t2.b ORDER BY t1.b LIMIT 1) AS c FROM t2;
+
+DROP TABLE t1,t2;
+
+--echo # end of 10.1 tests
+
+--echo #
 --echo # MDEV-13994: Bad join results with orderby_uses_equalities=on
 --echo #
 
diff --git a/mysql-test/main/temp_table_symlink.result b/mysql-test/main/temp_table_symlink.result
new file mode 100644
index 00000000000..1c5c68170ff
--- /dev/null
+++ b/mysql-test/main/temp_table_symlink.result
@@ -0,0 +1,13 @@
+create table d1 (a int);
+create temporary table t1 (a int);
+create temporary table t2 (a int);
+Got one of the listed errors
+create temporary table t3 (a int) engine=Aria;
+Got one of the listed errors
+select * from information_schema.columns where table_schema='test';
+Got one of the listed errors
+flush tables;
+select * from d1;
+a
+drop temporary table t1;
+drop table d1;
diff --git a/mysql-test/main/temp_table_symlink.test b/mysql-test/main/temp_table_symlink.test
new file mode 100644
index 00000000000..9297b472805
--- /dev/null
+++ b/mysql-test/main/temp_table_symlink.test
@@ -0,0 +1,32 @@
+source include/not_windows.inc;
+
+#
+# MDEV-23569 temporary tables can overwrite existing files
+#
+
+let datadir=`select @@datadir`;
+create table d1 (a int);
+create temporary table t1 (a int);
+perl;
+chdir "$ENV{MYSQL_TMP_DIR}/mysqld.1/";
+for (<#sql*.MYI>) {
+  /^#sql(.*)_([0-9a-f]+_)([0-9a-f]+)\.MYI$/ or die;
+  symlink "$ENV{datadir}/test/d1.MYI", sprintf "#sql$1_$2%x.MYI", hex($3)+1;
+  symlink "$ENV{datadir}/test/d1.MYI", sprintf "#sql$1_$2%x.MAI", hex($3)+1;
+  symlink "$ENV{datadir}/test/d1.MYI", sprintf "#sql$1_$2%x.MAI", hex($3)+2;
+  symlink "$ENV{datadir}/test/d1.MYI", "#sql_$1_0.MAI";
+}
+EOF
+
+error 1,1030;
+create temporary table t2 (a int);
+error 1,1030;
+create temporary table t3 (a int) engine=Aria;
+error 1,1030;
+select * from information_schema.columns where table_schema='test';
+
+flush tables;
+select * from d1;
+drop temporary table t1;
+remove_files_wildcard $MYSQL_TMP_DIR/mysqld.1 *sql*;
+drop table d1;