Merge 10.4 into 10.5

17 files changed, 755 insertions, 669 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..195456958d8
--- /dev/null
+++ b/mysql-test/main/innodb_ext_key,off.rdiff
@@ -0,0 +1,295 @@
+@@ -9,7 +9,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(*)
+@@ -19,7 +19,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
+@@ -50,7 +50,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;
+@@ -61,7 +61,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
+@@ -71,7 +71,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;
+@@ -84,7 +84,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
+@@ -93,7 +93,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)
+@@ -103,7 +103,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
+@@ -113,7 +113,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;
+@@ -124,7 +124,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
+@@ -134,7 +134,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;
+@@ -145,7 +145,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
+@@ -157,7 +157,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)
+@@ -171,10 +171,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
+@@ -183,7 +183,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)
+@@ -198,10 +198,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
+@@ -209,7 +209,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
+@@ -223,7 +223,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
+@@ -233,7 +233,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;
+@@ -251,9 +251,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
+@@ -263,7 +263,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;
+@@ -273,9 +273,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
+@@ -291,7 +291,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),
+@@ -305,7 +305,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
+@@ -322,8 +322,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
+@@ -502,7 +502,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   
+@@ -722,13 +722,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'
+@@ -763,11 +763,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'",
+@@ -796,8 +797,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 b0792605e54..e00192e1d64 100644
--- a/mysql-test/main/innodb_ext_key.result
+++ b/mysql-test/main/innodb_ext_key.result
@@ -1,5 +1,3 @@
-DROP TABLE IF EXISTS t1,t2,t3,t4;
-DROP DATABASE IF EXISTS dbt3_s001;
 SET SESSION DEFAULT_STORAGE_ENGINE='InnoDB';
 set @innodb_stats_persistent_save= @@innodb_stats_persistent;
 set @innodb_stats_persistent_sample_pages_save=
@@ -8,28 +6,6 @@ set global innodb_stats_persistent= 1;
 set global innodb_stats_persistent_sample_pages=100;
 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
@@ -49,29 +25,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';
@@ -93,29 +46,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;
@@ -137,31 +67,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;
@@ -185,27 +90,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
@@ -225,29 +109,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;
@@ -269,29 +130,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;
@@ -313,34 +151,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)
@@ -367,35 +177,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)
@@ -423,33 +204,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
@@ -475,38 +229,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;
@@ -537,30 +259,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;
@@ -584,37 +282,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),
@@ -651,7 +318,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;
@@ -672,7 +338,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);
@@ -693,9 +358,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;
@@ -725,21 +388,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);
@@ -752,7 +400,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 
@@ -761,12 +408,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
@@ -791,26 +436,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 (
@@ -820,29 +455,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	#	
-1	SIMPLE	t2	eq_ref	a	a	4	test.t1.a	#	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
@@ -881,18 +493,6 @@ test.t1	analyze	status	Engine-independent statistics collected
 test.t1	analyze	status	OK
 test.t3	analyze	status	Engine-independent statistics collected
 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
@@ -907,7 +507,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',
@@ -1011,16 +610,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'
@@ -1044,7 +633,6 @@ test.t1	analyze	status	Engine-independent statistics collected
 test.t1	analyze	status	OK
 test.t2	analyze	status	Engine-independent statistics collected
 test.t2	analyze	status	OK
-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	2	Using where
@@ -1059,14 +647,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
@@ -1084,7 +664,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 926a3472a6a..2f6097c7850 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 DEFAULT_STORAGE_ENGINE='InnoDB';
 
@@ -26,32 +21,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';
@@ -60,16 +35,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;
@@ -78,16 +43,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;
@@ -96,21 +51,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;
@@ -119,25 +65,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;
@@ -146,7 +73,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)
@@ -159,20 +85,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)
@@ -185,31 +97,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
-        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=on';
 explain
 select l_orderkey, l_linenumber from lineitem 
   where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000
@@ -220,17 +107,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=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
@@ -240,17 +116,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
@@ -262,23 +127,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
@@ -303,8 +151,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;
@@ -327,7 +173,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);
@@ -346,10 +191,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');
@@ -389,24 +231,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 #
@@ -420,13 +250,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;
@@ -455,24 +283,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 #
@@ -481,8 +296,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);
@@ -496,15 +309,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';
---replace_column 9 #
-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';
 --replace_column 9 #
 explain 
 select * from t1, t2 where t2.a=t1.a and t2.b < 2;
@@ -531,15 +335,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;
@@ -553,8 +348,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',
@@ -662,13 +455,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'
@@ -689,14 +475,10 @@ insert into t2 (b) values (null), (null), (null);
 
 analyze table t1,t2;
 
-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;
@@ -706,8 +488,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/mysqlbinlog_row_minimal.result b/mysql-test/main/mysqlbinlog_row_minimal.result
index f385b4ca69a..659228a57e2 100644
--- a/mysql-test/main/mysqlbinlog_row_minimal.result
+++ b/mysql-test/main/mysqlbinlog_row_minimal.result
@@ -327,6 +327,24 @@ ROLLBACK /* added by mysqlbinlog */;
 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
 DROP TABLE t1,t2;
+#
+# MDEV-16372 ER_BASE64_DECODE_ERROR upon replaying binary log with system table
+#
+FLUSH BINARY LOGS;
+CREATE TABLE t1 (pk INT PRIMARY KEY);
+INSERT INTO t1 VALUES (1);
+INSERT INTO t1 VALUES (1), (2) ON DUPLICATE KEY UPDATE pk= pk + 10;
+FLUSH BINARY LOGS;
+Proof: two subsequent patterns must be found
+FOUND 1 /### UPDATE `test`.`t1`/ in mysqlbinlog.sql
+FOUND 2 /### INSERT INTO `test`.`t1`/ in mysqlbinlog.sql
+DROP TABLE t1;
+SELECT * FROM t1;
+pk
+2
+11
+# Cleanup
+DROP TABLE t1;
 CREATE TABLE `t1` (
 `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
 `is_deleted` BIT(1) DEFAULT b'0',
@@ -351,23 +369,23 @@ FLUSH BINARY LOGS;
 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
-# at 387
-#<date> server id 1  end_log_pos 429 CRC32 XXX 	GTID 0-1-16
+# at POS
+#<date> server id 1  end_log_pos END_LOG_POS CRC32 XXX 	GTID D-S-N
 /*!100101 SET @@session.skip_parallel_replication=0*//*!*/;
 /*!100001 SET @@session.gtid_domain_id=0*//*!*/;
 /*!100001 SET @@session.server_id=1*//*!*/;
-/*!100001 SET @@session.gtid_seq_no=16*//*!*/;
+/*!100001 SET @@session.gtid_seq_no=21*//*!*/;
 START TRANSACTION
 /*!*/;
-# at 429
-# at 543
-#<date> server id 1  end_log_pos 543 CRC32 XXX 	Annotate_rows:
+# at POS
+# at POS
+#<date> server id 1  end_log_pos END_LOG_POS CRC32 XXX 	Annotate_rows:
 #Q> UPDATE t1 t1 INNER JOIN t2 t2 ON t1.ref_id = t2.id
 #Q> SET t1.is_deleted = TRUE
 #Q> WHERE t1.id =
-#<date> server id 1  end_log_pos 594 CRC32 XXX 	Table_map: `test`.`t1` mapped to number 35
-# at 594
-#<date> server id 1  end_log_pos 643 CRC32 XXX 	Update_rows: table id 35 flags: STMT_END_F
+#<date> server id 1  end_log_pos END_LOG_POS CRC32 XXX 	Table_map: `test`.`t1` mapped to number TID
+# at POS
+#<date> server id 1  end_log_pos END_LOG_POS CRC32 XXX 	Update_rows: table id TID flags: STMT_END_F
 ### UPDATE `test`.`t1`
 ### WHERE
 ###   @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
@@ -375,10 +393,10 @@ START TRANSACTION
 ###   @2=b'1' /* BIT(1) meta=1 nullable=1 is_null=0 */
 ###   @3=X /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
 # Number of rows: 1
-# at 643
-#<date> server id 1  end_log_pos 725 CRC32 XXX 	Query	thread_id=5	exec_time=x	error_code=0
+# at POS
+#<date> server id 1  end_log_pos END_LOG_POS CRC32 XXX 	Query	thread_id=TID	exec_time=x	error_code=0
 SET TIMESTAMP=X/*!*/;
-SET @@session.pseudo_thread_id=5/*!*/;
+SET @@session.pseudo_thread_id=TID/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1, @@session.sql_if_exists=0/*!*/;
 SET @@session.sql_mode=#/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
@@ -388,8 +406,6 @@ SET @@session.lc_time_names=0/*!*/;
 SET @@session.collation_database=DEFAULT/*!*/;
 COMMIT
 /*!*/;
-# at 725
-#<date> server id 1  end_log_pos 773 CRC32 XXX 	Rotate to master-bin.000004  pos: 4
 DELIMITER ;
 # End of log file
 ROLLBACK /* added by mysqlbinlog */;
diff --git a/mysql-test/main/mysqlbinlog_row_minimal.test b/mysql-test/main/mysqlbinlog_row_minimal.test
index b69aa6da14b..b399cc4ac45 100644
--- a/mysql-test/main/mysqlbinlog_row_minimal.test
+++ b/mysql-test/main/mysqlbinlog_row_minimal.test
@@ -33,6 +33,33 @@ FLUSH BINARY LOGS;
 
 DROP TABLE t1,t2;
 
+--echo #
+--echo # MDEV-16372 ER_BASE64_DECODE_ERROR upon replaying binary log with system table
+--echo #
+FLUSH BINARY LOGS;
+CREATE TABLE t1 (pk INT PRIMARY KEY);
+INSERT INTO t1 VALUES (1);
+INSERT INTO t1 VALUES (1), (2) ON DUPLICATE KEY UPDATE pk= pk + 10;
+
+--let $binlog = query_get_value(SHOW MASTER STATUS, File, 1)
+
+FLUSH BINARY LOGS;
+--exec $MYSQL_BINLOG --verbose $datadir/$binlog > $MYSQLTEST_VARDIR/tmp/mysqlbinlog.sql
+--echo Proof: two subsequent patterns must be found
+--let SEARCH_PATTERN= ### UPDATE `test`.`t1`
+--let SEARCH_FILE= $MYSQLTEST_VARDIR/tmp/mysqlbinlog.sql
+--source include/search_pattern_in_file.inc
+--let SEARCH_PATTERN= ### INSERT INTO `test`.`t1`
+--let SEARCH_FILE= $MYSQLTEST_VARDIR/tmp/mysqlbinlog.sql
+--source include/search_pattern_in_file.inc
+
+DROP TABLE t1;
+--exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/mysqlbinlog.sql
+SELECT * FROM t1;
+
+--echo # Cleanup
+DROP TABLE t1;
+--remove_file $MYSQLTEST_VARDIR/tmp/mysqlbinlog.sql
 #
 # MDEV-14605 ON UPDATE CURRENT_TIMESTAMP fields by multi-table UPDATE are not logged with binlog_row_image=MINIMAL
 #
@@ -64,11 +91,12 @@ UPDATE t1 t1 INNER JOIN t2 t2 ON t1.ref_id = t2.id
   WHERE t1.id = 1;
 
 --let $binlog = query_get_value(SHOW MASTER STATUS, File, 1)
+--let $binlog_end= query_get_value(SHOW MASTER STATUS, Position, 1)
 
 FLUSH BINARY LOGS;
 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
---replace_regex /\d{6} *\d*:\d\d:\d\d/<date>/ /SET TIMESTAMP=\d*/SET TIMESTAMP=X/ /exec_time=\d*/exec_time=x/ /@3=\d*/@3=X/ /CRC32 0x[0-9a-f]+/CRC32 XXX/ /@@session.sql_mode=\d+/@@session.sql_mode=#/ /collation_server=\d+/collation_server=#/
---exec $MYSQL_BINLOG --verbose --verbose --base64-output=DECODE-ROWS $datadir/$binlog --start-position=$binlog_pos
+--replace_regex /table id \d*/table id TID/ /mapped to number \d*/mapped to number TID/ /at \d*/at POS/ /end_log_pos \d*/end_log_pos END_LOG_POS/ /GTID \d*-\d*-\d*/GTID D-S-N/ /\d{6} *\d*:\d\d:\d\d/<date>/ /SET TIMESTAMP=\d*/SET TIMESTAMP=X/ /exec_time=\d*/exec_time=x/ /@3=\d*/@3=X/ /CRC32 0x[0-9a-f]+/CRC32 XXX/ /@@session.sql_mode=\d+/@@session.sql_mode=#/ /collation_server=\d+/collation_server=#/ /thread_id=\d*/thread_id=TID/
+--exec $MYSQL_BINLOG --verbose --verbose --base64-output=DECODE-ROWS $datadir/$binlog --start-position=$binlog_pos --stop-position=$binlog_end
 
 DROP TABLE t1,t2;
 
diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result
index de0d3dc4003..a3811ca61e2 100644
--- a/mysql-test/main/order_by.result
+++ b/mysql-test/main/order_by.result
@@ -3433,6 +3433,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 038bf82b0fb..8e0b479e02d 100644
--- a/mysql-test/main/order_by.test
+++ b/mysql-test/main/order_by.test
@@ -2203,6 +2203,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/parser.result b/mysql-test/main/parser.result
index d37b76e5f74..1d3acaf062b 100644
--- a/mysql-test/main/parser.result
+++ b/mysql-test/main/parser.result
@@ -1945,6 +1945,95 @@ KILL ( SELECT 1 ) + LASTVAL(s);
 ERROR 42000: KILL does not support subqueries or stored functions
 KILL LASTVAL(s);
 ERROR 42000: KILL does not support subqueries or stored functions
+#
+# MDEV-23094: Multiple calls to a Stored Procedure from another
+# Stored Procedure crashes server
+#
+create table t1 (id1 int primary key, data1 int);
+create table t2 (id2 int primary key, data2 int);
+create procedure p1(IN id int, IN dt int)
+begin
+if (exists(select * from t1 where id1 = id and data1 = dt) or
+not exists (select * from t2 where id2 = id and data2 = dt))
+then
+select 1;
+end if;
+end //
+call p1(1,2);
+1
+1
+call p1(1,2);
+1
+1
+drop procedure p1;
+create procedure p1(IN id int, IN dt int)
+begin
+case (exists(select * from t1 where id1 = id and data1 = dt) or
+not exists (select * from t2 where id2 = id and data2 = dt))
+when 1 then
+select 1;
+else
+select 0;
+end case;
+end //
+call p1(1,2);
+1
+1
+call p1(1,2);
+1
+1
+drop procedure p1;
+create procedure p1(IN id int, IN dt int)
+begin
+declare wcont int default 1;
+while (exists(select * from t1 where id1 = id and data1 = dt) or
+not exists (select * from t2 where id2 = id and data2 = dt)) and wcont
+do
+select 1;
+set wcont=0;
+end while;
+end //
+call p1(1,2);
+1
+1
+call p1(1,2);
+1
+1
+drop procedure p1;
+create procedure p1(IN id int, IN dt int)
+begin
+declare count int default 1;
+repeat
+select 1;
+set count=count+1;
+until (exists(select * from t1 where id1 = id and data1 = dt) or
+not exists (select * from t2 where id2 = id and data2 = dt)) and
+count < 3
+end repeat;
+end //
+call p1(1,2);
+1
+1
+call p1(1,2);
+1
+1
+drop procedure p1;
+create procedure p1(IN id int, IN dt int)
+begin
+for i in 1..(exists(select * from t1 where id1 = id and data1 = dt) or
+not exists (select * from t2 where id2 = id and data2 = dt))
+do
+select 1;
+end for;
+end //
+call p1(1,2);
+1
+1
+call p1(1,2);
+1
+1
+drop procedure p1;
+drop table t1,t2;
 # End of 10.4 tests
 #
 # Start of 10.5 tests
diff --git a/mysql-test/main/parser.test b/mysql-test/main/parser.test
index 3689f38dc7f..755cbb95c45 100644
--- a/mysql-test/main/parser.test
+++ b/mysql-test/main/parser.test
@@ -1698,6 +1698,103 @@ KILL ( SELECT 1 ) + LASTVAL(s);
 --error ER_SUBQUERIES_NOT_SUPPORTED
 KILL LASTVAL(s);
 
+--echo #
+--echo # MDEV-23094: Multiple calls to a Stored Procedure from another
+--echo # Stored Procedure crashes server
+--echo #
+
+create table t1 (id1 int primary key, data1 int);
+create table t2 (id2 int primary key, data2 int);
+
+delimiter //;
+create procedure p1(IN id int, IN dt int)
+begin
+  if (exists(select * from t1 where id1 = id and data1 = dt) or
+      not exists (select * from t2 where id2 = id and data2 = dt))
+  then
+      select 1;
+  end if;
+end //
+delimiter ;//
+
+call p1(1,2);
+call p1(1,2);
+
+drop procedure p1;
+
+delimiter //;
+create procedure p1(IN id int, IN dt int)
+begin
+case (exists(select * from t1 where id1 = id and data1 = dt) or
+      not exists (select * from t2 where id2 = id and data2 = dt))
+when 1 then
+  select 1;
+else
+  select 0;
+end case;
+end //
+delimiter ;//
+
+call p1(1,2);
+call p1(1,2);
+
+drop procedure p1;
+
+delimiter //;
+create procedure p1(IN id int, IN dt int)
+begin
+declare wcont int default 1;
+while (exists(select * from t1 where id1 = id and data1 = dt) or
+      not exists (select * from t2 where id2 = id and data2 = dt)) and wcont
+do
+  select 1;
+  set wcont=0;
+end while;
+end //
+delimiter ;//
+
+call p1(1,2);
+call p1(1,2);
+
+drop procedure p1;
+
+delimiter //;
+create procedure p1(IN id int, IN dt int)
+begin
+declare count int default 1;
+repeat
+  select 1;
+  set count=count+1;
+until (exists(select * from t1 where id1 = id and data1 = dt) or
+      not exists (select * from t2 where id2 = id and data2 = dt)) and
+      count < 3
+end repeat;
+end //
+delimiter ;//
+
+call p1(1,2);
+call p1(1,2);
+
+drop procedure p1;
+
+delimiter //;
+create procedure p1(IN id int, IN dt int)
+begin
+for i in 1..(exists(select * from t1 where id1 = id and data1 = dt) or
+             not exists (select * from t2 where id2 = id and data2 = dt))
+do
+  select 1;
+end for;
+end //
+delimiter ;//
+
+call p1(1,2);
+call p1(1,2);
+
+drop procedure p1;
+
+drop table t1,t2;
+
 --echo # End of 10.4 tests
 
 
diff --git a/mysql-test/main/rowid_filter.result b/mysql-test/main/rowid_filter.result
index 6fda4b03b0f..83cf932bdae 100644
--- a/mysql-test/main/rowid_filter.result
+++ b/mysql-test/main/rowid_filter.result
@@ -2165,6 +2165,12 @@ A.a+1000*B.a + 10000,
 'filler-data-filler-data'
 from
 t11 A, t10 B;
+analyze table t1 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	Warning	Engine-independent statistics are not collected for column 'el_index'
+test.t1	analyze	Warning	Engine-independent statistics are not collected for column 'filler'
+test.t1	analyze	status	Table is already up to date
 # This must not use rowid_filter with key=el_index|el_index_60:
 explain
 select * from t1
diff --git a/mysql-test/main/rowid_filter.test b/mysql-test/main/rowid_filter.test
index c47710e65b8..ed1227d3770 100644
--- a/mysql-test/main/rowid_filter.test
+++ b/mysql-test/main/rowid_filter.test
@@ -377,6 +377,7 @@ select
   'filler-data-filler-data'
 from
   t11 A, t10 B;
+analyze table t1 persistent for all;
 
 --echo # This must not use rowid_filter with key=el_index|el_index_60:
 explain
diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result
index 15e77f5689e..b2d598ca229 100644
--- a/mysql-test/main/rowid_filter_innodb.result
+++ b/mysql-test/main/rowid_filter_innodb.result
@@ -2114,6 +2114,12 @@ A.a+1000*B.a + 10000,
 'filler-data-filler-data'
 from
 t11 A, t10 B;
+analyze table t1 persistent for all;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	Warning	Engine-independent statistics are not collected for column 'el_index'
+test.t1	analyze	Warning	Engine-independent statistics are not collected for column 'filler'
+test.t1	analyze	status	OK
 # This must not use rowid_filter with key=el_index|el_index_60:
 explain
 select * from t1
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..35f956e8890
--- /dev/null
+++ b/mysql-test/main/temp_table_symlink.test
@@ -0,0 +1,33 @@
+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-temptable-[0-9a-f]+)(-[0-9a-f]+-)([0-9a-f]+)\.MYI$/ or die $_;
+  symlink "$ENV{datadir}/test/d1.MYI", sprintf "$1$2%x.MYI", hex($3)+1;
+  symlink "$ENV{datadir}/test/d1.MYI", sprintf "$1$2%x.MAI", hex($3)+1;
+  symlink "$ENV{datadir}/test/d1.MYI", sprintf "$1$2%x.MAI", hex($3)+2;
+  symlink "$ENV{datadir}/test/d1.MYI", sprintf "$1$2%x.MAI", hex($3)+3;
+  symlink "$ENV{datadir}/test/d1.MYI", "$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;