MDEV-15777 Use inferred IS NOT NULL predicates in the range optimizer10.3-mdev15777

This patch introduces the optimization that allows range optimizer to
consider index range scans that are built employing NOT NULL predicates
inferred from WHERE conditions and ON expressions.
The patch adds a new optimizer switch not_null_range_scan.

26 files changed, 1377 insertions, 33 deletions

diff --git a/mysql-test/main/mysqld--help.result b/mysql-test/main/mysqld--help.result
index 1c7e9cd839d..19b4319910b 100644
--- a/mysql-test/main/mysqld--help.result
+++ b/mysql-test/main/mysqld--help.result
@@ -678,7 +678,8 @@ The following specify which files/extra groups are read (specified before remain
  join_cache_hashed, join_cache_bka, 
  optimize_join_buffer_size, table_elimination, 
  extended_keys, exists_to_in, orderby_uses_equalities, 
- condition_pushdown_for_derived, split_materialized
+ condition_pushdown_for_derived, split_materialized, 
+ not_null_range_scan
  --optimizer-use-condition-selectivity=# 
  Controls selectivity of which conditions the optimizer
  takes into account to calculate cardinality of a partial
diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result
index 32e0cf2868c..807ca160ff0 100644
--- a/mysql-test/main/range.result
+++ b/mysql-test/main/range.result
@@ -3024,3 +3024,288 @@ drop table t1;
 #
 # End of 10.2 tests
 #
+#
+# MDEV-15777: Use inferred IS NOT NULL predicates in the range optimizer
+#
+set @save_optimizer_switch= @@optimizer_switch;
+set @@optimizer_switch='not_null_range_scan=on';
+create table ten(a int);
+insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table one_k(a int);
+insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
+create table t1 (
+id int NOT NULL,
+subset_id int DEFAULT NULL,
+PRIMARY KEY (id),
+KEY t1_subset_id (subset_id));
+create table t2 (
+id int,
+col int NOT NULL,
+key (id)
+);
+insert into t1 select a,a from one_k limit 5;
+insert into t1 select a+5,NULL from one_k limit 995;
+insert into t2 select a,a from one_k;
+analyze table t1,t2;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+test.t2	analyze	status	Table is already up to date
+# expected for t1: range access and rows = 4 (not 1000)
+explain SELECT * FROM t1,t2 WHERE t1.subset_id=t2.id;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	t1_subset_id	t1_subset_id	5	NULL	4	Using index condition
+1	SIMPLE	t2	ref	id	id	5	test.t1.subset_id	1	
+SELECT * FROM t1,t2 WHERE t1.subset_id=t2.id;
+id	subset_id	id	col
+0	0	0	0
+1	1	1	1
+2	2	2	2
+3	3	3	3
+4	4	4	4
+# with a subquery
+# expected the same plan as above
+explain SELECT * FROM t1 WHERE t1.subset_id IN (SELECT t2.id FROM t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	range	t1_subset_id	t1_subset_id	5	NULL	4	Using index condition
+1	PRIMARY	t2	ref	id	id	5	test.t1.subset_id	1	Using index; FirstMatch(t1)
+SELECT * FROM t1 WHERE t1.subset_id IN (SELECT t2.id FROM t2);
+id	subset_id
+0	0
+1	1
+2	2
+3	3
+4	4
+# non-mergable subquery
+# expected for t1: range access and rows = 4 (not 1000)
+explain SELECT * FROM t1
+WHERE t1.subset_id IN (SELECT max(t2.id) FROM t2 group by t2.col);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	range	t1_subset_id	t1_subset_id	5	NULL	4	Using index condition
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	test.t1.subset_id	1	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	1000	Using temporary
+SELECT * FROM t1
+WHERE t1.subset_id IN (SELECT max(t2.id) FROM t2 group by t2.col);
+id	subset_id
+0	0
+1	1
+2	2
+3	3
+4	4
+create view v1 as SELECT t2.id FROM t2;
+create view v2 as SELECT t2.id FROM t2 group by t2.col;
+# with mergeable view
+# expected for t1: range access and rows = 4 (not 1000)
+explain SELECT * FROM t1, v1 where t1.subset_id=v1.id;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	t1_subset_id	t1_subset_id	5	NULL	4	Using index condition
+1	SIMPLE	t2	ref	id	id	5	test.t1.subset_id	1	Using index
+SELECT * FROM t1, v1 where t1.subset_id=v1.id;
+id	subset_id	id
+0	0	0
+1	1	1
+2	2	2
+3	3	3
+4	4	4
+# with non-mergeable view
+# expected for t1: range access and rows = 4 (not 1000)
+explain SELECT * FROM t1, v2 where t1.subset_id=v2.id;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	range	t1_subset_id	t1_subset_id	5	NULL	4	Using index condition
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.subset_id	10	
+2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	1000	Using temporary; Using filesort
+SELECT * FROM t1, v2 where t1.subset_id=v2.id;
+id	subset_id	id
+0	0	0
+1	1	1
+2	2	2
+3	3	3
+4	4	4
+# expected for t2 and for t1: range access
+explain SELECT * FROM t2 LEFT JOIN t1 ON t1.subset_id != 5 WHERE t2.id in (0,2,4);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	range	id	id	5	NULL	3	Using index condition
+1	SIMPLE	t1	range	t1_subset_id	t1_subset_id	5	NULL	4	Using where; Using join buffer (flat, BNL join)
+SELECT * FROM t2 LEFT JOIN t1 ON t1.subset_id != 5 WHERE t2.id in (0,2,4);
+id	col	id	subset_id
+0	0	0	0
+2	2	0	0
+4	4	0	0
+0	0	1	1
+2	2	1	1
+4	4	1	1
+0	0	2	2
+2	2	2	2
+4	4	2	2
+0	0	3	3
+2	2	3	3
+4	4	3	3
+0	0	4	4
+2	2	4	4
+4	4	4	4
+# no range access expected for t1
+explain SELECT * FROM t1 LEFT JOIN t2 ON t1.subset_id=t2.id LIMIT 10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1000	
+1	SIMPLE	t2	ref	id	id	5	test.t1.subset_id	1	Using where
+SELECT * FROM t1 LEFT JOIN t2 ON t1.subset_id=t2.id LIMIT 10;
+id	subset_id	id	col
+0	0	0	0
+1	1	1	1
+2	2	2	2
+3	3	3	3
+4	4	4	4
+5	NULL	NULL	NULL
+6	NULL	NULL	NULL
+7	NULL	NULL	NULL
+8	NULL	NULL	NULL
+9	NULL	NULL	NULL
+# expected for t1: range access
+explain SELECT * FROM ten LEFT JOIN (t1,t2) ON ten.a=t2.col AND t1.subset_id=t2.id;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	ten	ALL	NULL	NULL	NULL	NULL	10	
+1	SIMPLE	t1	range	t1_subset_id	t1_subset_id	5	NULL	4	Using where
+1	SIMPLE	t2	ref	id	id	5	test.t1.subset_id	1	Using where
+SELECT * FROM ten LEFT JOIN (t1,t2) ON ten.a=t2.col AND t1.subset_id=t2.id;
+a	id	subset_id	id	col
+0	0	0	0	0
+1	1	1	1	1
+2	2	2	2	2
+3	3	3	3	3
+4	4	4	4	4
+5	NULL	NULL	NULL	NULL
+6	NULL	NULL	NULL	NULL
+7	NULL	NULL	NULL	NULL
+8	NULL	NULL	NULL	NULL
+9	NULL	NULL	NULL	NULL
+# no range access expected for t1
+explain SELECT * FROM t1 LEFT JOIN (t2,ten) ON ten.a=t2.col AND t1.subset_id=t2.id
+LIMIT 10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1000	
+1	SIMPLE	t2	ref	id	id	5	test.t1.subset_id	1	Using where
+1	SIMPLE	ten	ALL	NULL	NULL	NULL	NULL	10	Using where
+SELECT * FROM t1 LEFT JOIN (t2,ten) ON ten.a=t2.col AND t1.subset_id=t2.id
+LIMIT 10;
+id	subset_id	id	col	a
+0	0	0	0	0
+1	1	1	1	1
+2	2	2	2	2
+3	3	3	3	3
+4	4	4	4	4
+5	NULL	NULL	NULL	NULL
+6	NULL	NULL	NULL	NULL
+7	NULL	NULL	NULL	NULL
+8	NULL	NULL	NULL	NULL
+9	NULL	NULL	NULL	NULL
+drop index id on t2;
+# expected for t1: range access
+explain SELECT * FROM t1,t2 WHERE t1.subset_id=t2.id;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	t1_subset_id	t1_subset_id	5	NULL	4	Using index condition
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1000	Using where; Using join buffer (flat, BNL join)
+SELECT * FROM t1,t2 WHERE t1.subset_id=t2.id;
+id	subset_id	id	col
+0	0	0	0
+1	1	1	1
+2	2	2	2
+3	3	3	3
+4	4	4	4
+# expected impossible where after reading const tables
+explain SELECT * FROM t1,t2 WHERE t1.subset_id > t2.id AND t1.subset_id IS NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+SELECT * FROM t1,t2 WHERE t1.subset_id > t2.id AND t1.subset_id IS NULL;
+id	subset_id	id	col
+# expected impossible where after reading const tables
+explain SELECT * FROM t1,t2 WHERE t1.subset_id > t2.id AND t2.id IS NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+SELECT * FROM t1,t2 WHERE t1.subset_id > t2.id AND t2.id IS NULL;
+id	subset_id	id	col
+drop index t1_subset_id on t1;
+alter table t1 add column m int not null default 0;
+alter table t1 add index idx(m,subset_id);
+alter table t2 add index (id);
+update t1 set m = id mod 2;
+analyze table t1,t2;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+test.t2	analyze	status	Table is already up to date
+# expected for t1: range access by idx (keylen=9)
+explain SELECT * FROM t1,t2 WHERE t1.subset_id=t2.id and t1.m=0 ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	idx	idx	9	NULL	4	Using index condition
+1	SIMPLE	t2	ref	id	id	5	test.t1.subset_id	1	
+SELECT * FROM t1,t2 WHERE t1.subset_id=t2.id and t1.m=0 ;
+id	subset_id	m	id	col
+0	0	0	0	0
+2	2	0	2	2
+4	4	0	4	4
+drop view v1,v2;
+drop table t1,t2;
+create table t1 (
+id int NOT NULL,
+subset_id int DEFAULT NULL,
+KEY key1(id, subset_id),
+KEY t1_subset_id (subset_id)
+);
+create table t2 (
+id int NOT NULL,
+col int NOT NULL,
+key (id)
+);
+insert into t1 select 1,a from one_k limit 5;
+insert into t1 select 1,NULL from one_k limit 495;
+insert into t2 select a,a from one_k;
+analyze table t1,t2;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+test.t2	analyze	status	Table is already up to date
+# expected for t1 :range access by index key1
+# rows 4 instead of 500
+explain SELECT * FROM t1,t2 WHERE t1.id>=1 and t1.subset_id=t2.id;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	key1,t1_subset_id	key1	9	NULL	4	Using where; Using index
+1	SIMPLE	t2	ref	id	id	4	test.t1.subset_id	1	
+SELECT * FROM t1,t2 WHERE t1.id>=1 and t1.subset_id=t2.id;
+id	subset_id	id	col
+1	0	0	0
+1	1	1	1
+1	2	2	2
+1	3	3	3
+1	4	4	4
+drop table t1,t2;
+create table t1 (id int unsigned,col int, KEY key1(id));
+create table t2 (id int unsigned,col int DEFAULT NULL,key (id));
+insert into t1 select a,2 from one_k limit 50;
+insert into t1 select NULL,2 from one_k limit 450;
+insert into t2 select a,a from one_k;
+insert into t2 select a,a from one_k;
+analyze table t1,t2;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+test.t2	analyze	status	OK
+# using key1 for range access on t1 and also using index for sorting,
+# no filesort, rows should be 75 not 500
+explain SELECT * FROM t1,t2 WHERE t1.id=t2.id AND t1.col=2 ORDER BY t2.id LIMIT 10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	key1	key1	5	NULL	75	Using index condition; Using where
+1	SIMPLE	t2	ref	id	id	5	test.t1.id	2	
+SELECT * FROM t1,t2 WHERE t1.id=t2.id AND t1.col=2 ORDER BY t2.id LIMIT 10;
+id	col	id	col
+0	2	0	0
+0	2	0	0
+1	2	1	1
+1	2	1	1
+2	2	2	2
+2	2	2	2
+3	2	3	3
+3	2	3	3
+4	2	4	4
+4	2	4	4
+drop table t1,t2;
+drop table ten,one_k;
+set @@optimizer_switch= @save_optimizer_switch;
+#
+# End of 10.3 tests
+#
diff --git a/mysql-test/main/range.test b/mysql-test/main/range.test
index bd2299bac5f..014916a4d7f 100644
--- a/mysql-test/main/range.test
+++ b/mysql-test/main/range.test
@@ -2053,3 +2053,185 @@ drop table t1;
 --echo #
 --echo # End of 10.2 tests
 --echo #
+
+--echo #
+--echo # MDEV-15777: Use inferred IS NOT NULL predicates in the range optimizer
+--echo #
+
+set @save_optimizer_switch= @@optimizer_switch;
+set @@optimizer_switch='not_null_range_scan=on';
+create table ten(a int);
+insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table one_k(a int);
+insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
+
+create table t1 (
+  id int NOT NULL,
+  subset_id int DEFAULT NULL,
+  PRIMARY KEY (id),
+  KEY t1_subset_id (subset_id));
+
+create table t2 (
+  id int,
+  col int NOT NULL,
+  key (id)
+);
+
+insert into t1 select a,a from one_k limit 5;
+insert into t1 select a+5,NULL from one_k limit 995;
+insert into t2 select a,a from one_k;
+
+analyze table t1,t2;
+
+let $q=
+SELECT * FROM t1,t2 WHERE t1.subset_id=t2.id;
+--echo # expected for t1: range access and rows = 4 (not 1000)
+eval explain $q;
+eval $q;
+
+let $q=
+SELECT * FROM t1 WHERE t1.subset_id IN (SELECT t2.id FROM t2);
+--echo # with a subquery
+--echo # expected the same plan as above
+eval explain $q;
+eval $q;
+
+let $q=
+SELECT * FROM t1
+  WHERE t1.subset_id IN (SELECT max(t2.id) FROM t2 group by t2.col);
+--echo # non-mergable subquery
+--echo # expected for t1: range access and rows = 4 (not 1000)
+eval explain $q;
+eval $q;
+
+create view v1 as SELECT t2.id FROM t2;
+create view v2 as SELECT t2.id FROM t2 group by t2.col;
+
+let $q=
+SELECT * FROM t1, v1 where t1.subset_id=v1.id;
+--echo # with mergeable view
+--echo # expected for t1: range access and rows = 4 (not 1000)
+eval explain $q;
+eval $q;
+
+let $q= SELECT * FROM t1, v2 where t1.subset_id=v2.id;
+--echo # with non-mergeable view
+--echo # expected for t1: range access and rows = 4 (not 1000)
+eval explain $q;
+eval $q;
+
+let $q=
+SELECT * FROM t2 LEFT JOIN t1 ON t1.subset_id != 5 WHERE t2.id in (0,2,4);
+--echo # expected for t2 and for t1: range access
+eval explain $q;
+eval $q;
+
+let $q=
+SELECT * FROM t1 LEFT JOIN t2 ON t1.subset_id=t2.id LIMIT 10;
+--echo # no range access expected for t1
+eval explain $q;
+eval $q;
+
+let $q=
+SELECT * FROM ten LEFT JOIN (t1,t2) ON ten.a=t2.col AND t1.subset_id=t2.id;
+--echo # expected for t1: range access
+eval explain $q;
+eval $q;
+
+let $q=
+SELECT * FROM t1 LEFT JOIN (t2,ten) ON ten.a=t2.col AND t1.subset_id=t2.id
+LIMIT 10;
+--echo # no range access expected for t1
+eval explain $q;
+eval $q;
+
+drop index id on t2;
+
+let $q=
+SELECT * FROM t1,t2 WHERE t1.subset_id=t2.id;
+--echo # expected for t1: range access
+eval explain $q;
+eval $q;
+
+let $q=
+SELECT * FROM t1,t2 WHERE t1.subset_id > t2.id AND t1.subset_id IS NULL;
+--echo # expected impossible where after reading const tables
+eval explain $q;
+eval $q;
+
+let $q=
+SELECT * FROM t1,t2 WHERE t1.subset_id > t2.id AND t2.id IS NULL;
+--echo # expected impossible where after reading const tables
+eval explain $q;
+eval $q;
+
+drop index t1_subset_id on t1;
+alter table t1 add column m int not null default 0;
+alter table t1 add index idx(m,subset_id);
+alter table t2 add index (id);
+update t1 set m = id mod 2;
+analyze table t1,t2;
+
+let $q=
+SELECT * FROM t1,t2 WHERE t1.subset_id=t2.id and t1.m=0 ;
+--echo # expected for t1: range access by idx (keylen=9)
+eval explain $q;
+eval $q;
+
+
+drop view v1,v2;
+drop table t1,t2;
+
+create table t1 (
+  id int NOT NULL,
+  subset_id int DEFAULT NULL,
+  KEY key1(id, subset_id),
+  KEY t1_subset_id (subset_id)
+);
+
+create table t2 (
+  id int NOT NULL,
+  col int NOT NULL,
+  key (id)
+);
+
+insert into t1 select 1,a from one_k limit 5;
+insert into t1 select 1,NULL from one_k limit 495;
+insert into t2 select a,a from one_k;
+
+analyze table t1,t2;
+
+let $q=
+SELECT * FROM t1,t2 WHERE t1.id>=1 and t1.subset_id=t2.id;
+--echo # expected for t1 :range access by index key1
+--echo # rows 4 instead of 500
+eval explain $q;
+eval $q;
+
+drop table t1,t2;
+
+create table t1 (id int unsigned,col int, KEY key1(id));
+create table t2 (id int unsigned,col int DEFAULT NULL,key (id));
+insert into t1 select a,2 from one_k limit 50;
+insert into t1 select NULL,2 from one_k limit 450;
+insert into t2 select a,a from one_k;
+insert into t2 select a,a from one_k;
+
+analyze table t1,t2;
+
+let $q=
+SELECT * FROM t1,t2 WHERE t1.id=t2.id AND t1.col=2 ORDER BY t2.id LIMIT 10;
+--echo # using key1 for range access on t1 and also using index for sorting,
+--echo # no filesort, rows should be 75 not 500
+eval explain $q;
+eval $q;
+
+drop table t1,t2;
+
+drop table ten,one_k;
+set @@optimizer_switch= @save_optimizer_switch;
+
+--echo #
+--echo # End of 10.3 tests
+--echo #
diff --git a/mysql-test/main/range_mrr_icp.result b/mysql-test/main/range_mrr_icp.result
index 6b5bf33239f..d484d512564 100644
--- a/mysql-test/main/range_mrr_icp.result
+++ b/mysql-test/main/range_mrr_icp.result
@@ -3036,4 +3036,289 @@ drop table t1;
 #
 # End of 10.2 tests
 #
+#
+# MDEV-15777: Use inferred IS NOT NULL predicates in the range optimizer
+#
+set @save_optimizer_switch= @@optimizer_switch;
+set @@optimizer_switch='not_null_range_scan=on';
+create table ten(a int);
+insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table one_k(a int);
+insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
+create table t1 (
+id int NOT NULL,
+subset_id int DEFAULT NULL,
+PRIMARY KEY (id),
+KEY t1_subset_id (subset_id));
+create table t2 (
+id int,
+col int NOT NULL,
+key (id)
+);
+insert into t1 select a,a from one_k limit 5;
+insert into t1 select a+5,NULL from one_k limit 995;
+insert into t2 select a,a from one_k;
+analyze table t1,t2;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+test.t2	analyze	status	Table is already up to date
+# expected for t1: range access and rows = 4 (not 1000)
+explain SELECT * FROM t1,t2 WHERE t1.subset_id=t2.id;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	t1_subset_id	t1_subset_id	5	NULL	4	Using index condition; Rowid-ordered scan
+1	SIMPLE	t2	ref	id	id	5	test.t1.subset_id	1	
+SELECT * FROM t1,t2 WHERE t1.subset_id=t2.id;
+id	subset_id	id	col
+0	0	0	0
+1	1	1	1
+2	2	2	2
+3	3	3	3
+4	4	4	4
+# with a subquery
+# expected the same plan as above
+explain SELECT * FROM t1 WHERE t1.subset_id IN (SELECT t2.id FROM t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	range	t1_subset_id	t1_subset_id	5	NULL	4	Using index condition; Rowid-ordered scan
+1	PRIMARY	t2	ref	id	id	5	test.t1.subset_id	1	Using index; FirstMatch(t1)
+SELECT * FROM t1 WHERE t1.subset_id IN (SELECT t2.id FROM t2);
+id	subset_id
+0	0
+1	1
+2	2
+3	3
+4	4
+# non-mergable subquery
+# expected for t1: range access and rows = 4 (not 1000)
+explain SELECT * FROM t1
+WHERE t1.subset_id IN (SELECT max(t2.id) FROM t2 group by t2.col);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	range	t1_subset_id	t1_subset_id	5	NULL	4	Using index condition; Rowid-ordered scan
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	test.t1.subset_id	1	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	1000	Using temporary
+SELECT * FROM t1
+WHERE t1.subset_id IN (SELECT max(t2.id) FROM t2 group by t2.col);
+id	subset_id
+0	0
+1	1
+2	2
+3	3
+4	4
+create view v1 as SELECT t2.id FROM t2;
+create view v2 as SELECT t2.id FROM t2 group by t2.col;
+# with mergeable view
+# expected for t1: range access and rows = 4 (not 1000)
+explain SELECT * FROM t1, v1 where t1.subset_id=v1.id;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	t1_subset_id	t1_subset_id	5	NULL	4	Using index condition; Rowid-ordered scan
+1	SIMPLE	t2	ref	id	id	5	test.t1.subset_id	1	Using index
+SELECT * FROM t1, v1 where t1.subset_id=v1.id;
+id	subset_id	id
+0	0	0
+1	1	1
+2	2	2
+3	3	3
+4	4	4
+# with non-mergeable view
+# expected for t1: range access and rows = 4 (not 1000)
+explain SELECT * FROM t1, v2 where t1.subset_id=v2.id;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	range	t1_subset_id	t1_subset_id	5	NULL	4	Using index condition; Rowid-ordered scan
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.subset_id	10	
+2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	1000	Using temporary; Using filesort
+SELECT * FROM t1, v2 where t1.subset_id=v2.id;
+id	subset_id	id
+0	0	0
+1	1	1
+2	2	2
+3	3	3
+4	4	4
+# expected for t2 and for t1: range access
+explain SELECT * FROM t2 LEFT JOIN t1 ON t1.subset_id != 5 WHERE t2.id in (0,2,4);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	range	id	id	5	NULL	3	Using index condition; Rowid-ordered scan
+1	SIMPLE	t1	range	t1_subset_id	t1_subset_id	5	NULL	4	Using where; Rowid-ordered scan; Using join buffer (flat, BNL join)
+SELECT * FROM t2 LEFT JOIN t1 ON t1.subset_id != 5 WHERE t2.id in (0,2,4);
+id	col	id	subset_id
+0	0	0	0
+2	2	0	0
+4	4	0	0
+0	0	1	1
+2	2	1	1
+4	4	1	1
+0	0	2	2
+2	2	2	2
+4	4	2	2
+0	0	3	3
+2	2	3	3
+4	4	3	3
+0	0	4	4
+2	2	4	4
+4	4	4	4
+# no range access expected for t1
+explain SELECT * FROM t1 LEFT JOIN t2 ON t1.subset_id=t2.id LIMIT 10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1000	
+1	SIMPLE	t2	ref	id	id	5	test.t1.subset_id	1	Using where
+SELECT * FROM t1 LEFT JOIN t2 ON t1.subset_id=t2.id LIMIT 10;
+id	subset_id	id	col
+0	0	0	0
+1	1	1	1
+2	2	2	2
+3	3	3	3
+4	4	4	4
+5	NULL	NULL	NULL
+6	NULL	NULL	NULL
+7	NULL	NULL	NULL
+8	NULL	NULL	NULL
+9	NULL	NULL	NULL
+# expected for t1: range access
+explain SELECT * FROM ten LEFT JOIN (t1,t2) ON ten.a=t2.col AND t1.subset_id=t2.id;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	ten	ALL	NULL	NULL	NULL	NULL	10	
+1	SIMPLE	t1	range	t1_subset_id	t1_subset_id	5	NULL	4	Using where; Rowid-ordered scan
+1	SIMPLE	t2	ref	id	id	5	test.t1.subset_id	1	Using where
+SELECT * FROM ten LEFT JOIN (t1,t2) ON ten.a=t2.col AND t1.subset_id=t2.id;
+a	id	subset_id	id	col
+0	0	0	0	0
+1	1	1	1	1
+2	2	2	2	2
+3	3	3	3	3
+4	4	4	4	4
+5	NULL	NULL	NULL	NULL
+6	NULL	NULL	NULL	NULL
+7	NULL	NULL	NULL	NULL
+8	NULL	NULL	NULL	NULL
+9	NULL	NULL	NULL	NULL
+# no range access expected for t1
+explain SELECT * FROM t1 LEFT JOIN (t2,ten) ON ten.a=t2.col AND t1.subset_id=t2.id
+LIMIT 10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1000	
+1	SIMPLE	t2	ref	id	id	5	test.t1.subset_id	1	Using where
+1	SIMPLE	ten	ALL	NULL	NULL	NULL	NULL	10	Using where
+SELECT * FROM t1 LEFT JOIN (t2,ten) ON ten.a=t2.col AND t1.subset_id=t2.id
+LIMIT 10;
+id	subset_id	id	col	a
+0	0	0	0	0
+1	1	1	1	1
+2	2	2	2	2
+3	3	3	3	3
+4	4	4	4	4
+5	NULL	NULL	NULL	NULL
+6	NULL	NULL	NULL	NULL
+7	NULL	NULL	NULL	NULL
+8	NULL	NULL	NULL	NULL
+9	NULL	NULL	NULL	NULL
+drop index id on t2;
+# expected for t1: range access
+explain SELECT * FROM t1,t2 WHERE t1.subset_id=t2.id;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	t1_subset_id	t1_subset_id	5	NULL	4	Using index condition; Rowid-ordered scan
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1000	Using where; Using join buffer (flat, BNL join)
+SELECT * FROM t1,t2 WHERE t1.subset_id=t2.id;
+id	subset_id	id	col
+0	0	0	0
+1	1	1	1
+2	2	2	2
+3	3	3	3
+4	4	4	4
+# expected impossible where after reading const tables
+explain SELECT * FROM t1,t2 WHERE t1.subset_id > t2.id AND t1.subset_id IS NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+SELECT * FROM t1,t2 WHERE t1.subset_id > t2.id AND t1.subset_id IS NULL;
+id	subset_id	id	col
+# expected impossible where after reading const tables
+explain SELECT * FROM t1,t2 WHERE t1.subset_id > t2.id AND t2.id IS NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+SELECT * FROM t1,t2 WHERE t1.subset_id > t2.id AND t2.id IS NULL;
+id	subset_id	id	col
+drop index t1_subset_id on t1;
+alter table t1 add column m int not null default 0;
+alter table t1 add index idx(m,subset_id);
+alter table t2 add index (id);
+update t1 set m = id mod 2;
+analyze table t1,t2;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+test.t2	analyze	status	Table is already up to date
+# expected for t1: range access by idx (keylen=9)
+explain SELECT * FROM t1,t2 WHERE t1.subset_id=t2.id and t1.m=0 ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	idx	idx	9	NULL	4	Using index condition; Rowid-ordered scan
+1	SIMPLE	t2	ref	id	id	5	test.t1.subset_id	1	
+SELECT * FROM t1,t2 WHERE t1.subset_id=t2.id and t1.m=0 ;
+id	subset_id	m	id	col
+0	0	0	0	0
+2	2	0	2	2
+4	4	0	4	4
+drop view v1,v2;
+drop table t1,t2;
+create table t1 (
+id int NOT NULL,
+subset_id int DEFAULT NULL,
+KEY key1(id, subset_id),
+KEY t1_subset_id (subset_id)
+);
+create table t2 (
+id int NOT NULL,
+col int NOT NULL,
+key (id)
+);
+insert into t1 select 1,a from one_k limit 5;
+insert into t1 select 1,NULL from one_k limit 495;
+insert into t2 select a,a from one_k;
+analyze table t1,t2;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+test.t2	analyze	status	Table is already up to date
+# expected for t1 :range access by index key1
+# rows 4 instead of 500
+explain SELECT * FROM t1,t2 WHERE t1.id>=1 and t1.subset_id=t2.id;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	key1,t1_subset_id	key1	9	NULL	4	Using where; Using index
+1	SIMPLE	t2	ref	id	id	4	test.t1.subset_id	1	
+SELECT * FROM t1,t2 WHERE t1.id>=1 and t1.subset_id=t2.id;
+id	subset_id	id	col
+1	0	0	0
+1	1	1	1
+1	2	2	2
+1	3	3	3
+1	4	4	4
+drop table t1,t2;
+create table t1 (id int unsigned,col int, KEY key1(id));
+create table t2 (id int unsigned,col int DEFAULT NULL,key (id));
+insert into t1 select a,2 from one_k limit 50;
+insert into t1 select NULL,2 from one_k limit 450;
+insert into t2 select a,a from one_k;
+insert into t2 select a,a from one_k;
+analyze table t1,t2;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+test.t2	analyze	status	OK
+# using key1 for range access on t1 and also using index for sorting,
+# no filesort, rows should be 75 not 500
+explain SELECT * FROM t1,t2 WHERE t1.id=t2.id AND t1.col=2 ORDER BY t2.id LIMIT 10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	key1	key1	5	NULL	75	Using index condition; Using where
+1	SIMPLE	t2	ref	id	id	5	test.t1.id	2	
+SELECT * FROM t1,t2 WHERE t1.id=t2.id AND t1.col=2 ORDER BY t2.id LIMIT 10;
+id	col	id	col
+0	2	0	0
+0	2	0	0
+1	2	1	1
+1	2	1	1
+2	2	2	2
+2	2	2	2
+3	2	3	3
+3	2	3	3
+4	2	4	4
+4	2	4	4
+drop table t1,t2;
+drop table ten,one_k;
+set @@optimizer_switch= @save_optimizer_switch;
+#
+# End of 10.3 tests
+#
 set optimizer_switch=@mrr_icp_extra_tmp;
diff --git a/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result b/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result
index 87c837986ac..ba40512ea76 100644
--- a/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result
+++ b/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result
@@ -1,63 +1,63 @@
 SET @start_global_value = @@global.optimizer_switch;
 SELECT @start_global_value;
 @start_global_value
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,not_null_range_scan=off
 select @@global.optimizer_switch;
 @@global.optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,not_null_range_scan=off
 select @@session.optimizer_switch;
 @@session.optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,not_null_range_scan=off
 show global variables like 'optimizer_switch';
 Variable_name	Value
-optimizer_switch	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on
+optimizer_switch	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,not_null_range_scan=off
 show session variables like 'optimizer_switch';
 Variable_name	Value
-optimizer_switch	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on
+optimizer_switch	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,not_null_range_scan=off
 select * from information_schema.global_variables where variable_name='optimizer_switch';
 VARIABLE_NAME	VARIABLE_VALUE
-OPTIMIZER_SWITCH	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on
+OPTIMIZER_SWITCH	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,not_null_range_scan=off
 select * from information_schema.session_variables where variable_name='optimizer_switch';
 VARIABLE_NAME	VARIABLE_VALUE
-OPTIMIZER_SWITCH	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on
+OPTIMIZER_SWITCH	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,not_null_range_scan=off
 set global optimizer_switch=10;
 set session optimizer_switch=5;
 select @@global.optimizer_switch;
 @@global.optimizer_switch
-index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off
+index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,not_null_range_scan=off
 select @@session.optimizer_switch;
 @@session.optimizer_switch
-index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off
+index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,not_null_range_scan=off
 set global optimizer_switch="index_merge_sort_union=on";
 set session optimizer_switch="index_merge=off";
 select @@global.optimizer_switch;
 @@global.optimizer_switch
-index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off
+index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,not_null_range_scan=off
 select @@session.optimizer_switch;
 @@session.optimizer_switch
-index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off
+index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,not_null_range_scan=off
 show global variables like 'optimizer_switch';
 Variable_name	Value
-optimizer_switch	index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off
+optimizer_switch	index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,not_null_range_scan=off
 show session variables like 'optimizer_switch';
 Variable_name	Value
-optimizer_switch	index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off
+optimizer_switch	index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,not_null_range_scan=off
 select * from information_schema.global_variables where variable_name='optimizer_switch';
 VARIABLE_NAME	VARIABLE_VALUE
-OPTIMIZER_SWITCH	index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off
+OPTIMIZER_SWITCH	index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,not_null_range_scan=off
 select * from information_schema.session_variables where variable_name='optimizer_switch';
 VARIABLE_NAME	VARIABLE_VALUE
-OPTIMIZER_SWITCH	index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off
+OPTIMIZER_SWITCH	index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,not_null_range_scan=off
 set session optimizer_switch="default";
 select @@session.optimizer_switch;
 @@session.optimizer_switch
-index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off
+index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,not_null_range_scan=off
 set optimizer_switch = replace(@@optimizer_switch, '=off', '=on');
 Warnings:
 Warning	1681	'engine_condition_pushdown=on' is deprecated and will be removed in a future release
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,not_null_range_scan=on
 set global optimizer_switch=1.1;
 ERROR 42000: Incorrect argument type to variable 'optimizer_switch'
 set global optimizer_switch=1e1;
@@ -69,4 +69,4 @@ ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'foobar'
 SET @@global.optimizer_switch = @start_global_value;
 SELECT @@global.optimizer_switch;
 @@global.optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,not_null_range_scan=off
diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result
index 89e5fef60e6..714b55c53f7 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result
@@ -2715,17 +2715,17 @@ ENUM_VALUE_LIST	NULL
 READ_ONLY	NO
 COMMAND_LINE_ARGUMENT	REQUIRED
 VARIABLE_NAME	OPTIMIZER_SWITCH
-SESSION_VALUE	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on
-GLOBAL_VALUE	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on
+SESSION_VALUE	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,not_null_range_scan=off
+GLOBAL_VALUE	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,not_null_range_scan=off
 GLOBAL_VALUE_ORIGIN	COMPILE-TIME
-DEFAULT_VALUE	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on
+DEFAULT_VALUE	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,not_null_range_scan=off
 VARIABLE_SCOPE	SESSION
 VARIABLE_TYPE	FLAGSET
 VARIABLE_COMMENT	Fine-tune the optimizer behavior
 NUMERIC_MIN_VALUE	NULL
 NUMERIC_MAX_VALUE	NULL
 NUMERIC_BLOCK_SIZE	NULL
-ENUM_VALUE_LIST	index_merge,index_merge_union,index_merge_sort_union,index_merge_intersection,index_merge_sort_intersection,engine_condition_pushdown,index_condition_pushdown,derived_merge,derived_with_keys,firstmatch,loosescan,materialization,in_to_exists,semijoin,partial_match_rowid_merge,partial_match_table_scan,subquery_cache,mrr,mrr_cost_based,mrr_sort_keys,outer_join_with_cache,semijoin_with_cache,join_cache_incremental,join_cache_hashed,join_cache_bka,optimize_join_buffer_size,table_elimination,extended_keys,exists_to_in,orderby_uses_equalities,condition_pushdown_for_derived,split_materialized,default
+ENUM_VALUE_LIST	index_merge,index_merge_union,index_merge_sort_union,index_merge_intersection,index_merge_sort_intersection,engine_condition_pushdown,index_condition_pushdown,derived_merge,derived_with_keys,firstmatch,loosescan,materialization,in_to_exists,semijoin,partial_match_rowid_merge,partial_match_table_scan,subquery_cache,mrr,mrr_cost_based,mrr_sort_keys,outer_join_with_cache,semijoin_with_cache,join_cache_incremental,join_cache_hashed,join_cache_bka,optimize_join_buffer_size,table_elimination,extended_keys,exists_to_in,orderby_uses_equalities,condition_pushdown_for_derived,split_materialized,not_null_range_scan,default
 READ_ONLY	NO
 COMMAND_LINE_ARGUMENT	REQUIRED
 VARIABLE_NAME	OPTIMIZER_USE_CONDITION_SELECTIVITY
diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
index ca875e7f644..dd9d9eb14f0 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
@@ -2925,17 +2925,17 @@ ENUM_VALUE_LIST	NULL
 READ_ONLY	NO
 COMMAND_LINE_ARGUMENT	REQUIRED
 VARIABLE_NAME	OPTIMIZER_SWITCH
-SESSION_VALUE	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on
-GLOBAL_VALUE	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on
+SESSION_VALUE	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,not_null_range_scan=off
+GLOBAL_VALUE	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,not_null_range_scan=off
 GLOBAL_VALUE_ORIGIN	COMPILE-TIME
-DEFAULT_VALUE	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on
+DEFAULT_VALUE	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,not_null_range_scan=off
 VARIABLE_SCOPE	SESSION
 VARIABLE_TYPE	FLAGSET
 VARIABLE_COMMENT	Fine-tune the optimizer behavior
 NUMERIC_MIN_VALUE	NULL
 NUMERIC_MAX_VALUE	NULL
 NUMERIC_BLOCK_SIZE	NULL
-ENUM_VALUE_LIST	index_merge,index_merge_union,index_merge_sort_union,index_merge_intersection,index_merge_sort_intersection,engine_condition_pushdown,index_condition_pushdown,derived_merge,derived_with_keys,firstmatch,loosescan,materialization,in_to_exists,semijoin,partial_match_rowid_merge,partial_match_table_scan,subquery_cache,mrr,mrr_cost_based,mrr_sort_keys,outer_join_with_cache,semijoin_with_cache,join_cache_incremental,join_cache_hashed,join_cache_bka,optimize_join_buffer_size,table_elimination,extended_keys,exists_to_in,orderby_uses_equalities,condition_pushdown_for_derived,split_materialized,default
+ENUM_VALUE_LIST	index_merge,index_merge_union,index_merge_sort_union,index_merge_intersection,index_merge_sort_intersection,engine_condition_pushdown,index_condition_pushdown,derived_merge,derived_with_keys,firstmatch,loosescan,materialization,in_to_exists,semijoin,partial_match_rowid_merge,partial_match_table_scan,subquery_cache,mrr,mrr_cost_based,mrr_sort_keys,outer_join_with_cache,semijoin_with_cache,join_cache_incremental,join_cache_hashed,join_cache_bka,optimize_join_buffer_size,table_elimination,extended_keys,exists_to_in,orderby_uses_equalities,condition_pushdown_for_derived,split_materialized,not_null_range_scan,default
 READ_ONLY	NO
 COMMAND_LINE_ARGUMENT	REQUIRED
 VARIABLE_NAME	OPTIMIZER_USE_CONDITION_SELECTIVITY
diff --git a/sql/item.cc b/sql/item.cc
index 3584230fb2a..9c956116f72 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -3556,6 +3556,16 @@ table_map Item_field::all_used_tables() const
 }
 
 
+bool Item_field::find_not_null_fields(table_map allowed)
+{
+  if (field->table->const_table)
+    return false;
+  if (!get_depended_from() && field->real_maybe_null())
+    bitmap_set_bit(&field->table->tmp_set, field->field_index);
+  return false;
+}
+
+
 /*
   @Note  thd->fatal_error can be set in case of OOM
 */
diff --git a/sql/item.h b/sql/item.h
index 2adc111db03..c2dbdb135f1 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -1818,6 +1818,44 @@ public:
   virtual bool set_fields_as_dependent_processor(void *arg) { return 0; }
   /*============== End of Item processor list ======================*/
 
+  /*
+    Given a condition P from the WHERE clause or from an ON expression of
+    the processed SELECT S and a set of join tables from S marked in the
+    parameter 'allowed'={T} a call of P->find_not_null_fields({T}) has to
+    find the set fields {F} of the tables from 'allowed' such that:
+    - each field from {F} is declared as nullable
+    - each record of table t from {T} that contains NULL as the value for at
+      at least one field from {F} can be ignored when building the result set
+      for S
+    It is assumed here that the condition P is conjunctive and all its column
+    references belong to T.
+
+    Examples:
+      CREATE TABLE t1 (a int, b int);
+      CREATE TABLE t2 (a int, b int);
+
+      SELECT * FROM t1,t2 WHERE t1.a=t2.a and t1.b > 5;
+      A call of find_not_null_fields() for the whole WHERE condition and {t1,t2}
+      should find {t1.a,t1.b,t2.a}
+
+      SELECT * FROM t1 LEFT JOIN ON (t1.a=t2.a and t2.a > t2.b);
+      A call of find_not_null_fields() for the ON expression and {t2}
+      should find {t2.a,t2.b}
+
+    The function returns TRUE if it succeeds to prove that all records of
+    a table from {T} can be ignored. Otherwise it always returns FALSE.
+
+    Example:
+      SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t2.a IS NULL;
+    A call of find_not_null_fields() for the WHERE condition and {t1,t2}
+    will return TRUE.
+
+    It is assumed that the implementation of this virtual function saves
+    the info on the found set of fields in the structures associates with
+    tables from {T}.
+  */
+  virtual bool find_not_null_fields(table_map allowed) { return false; }
+
   virtual Item *get_copy(THD *thd)=0;
 
   bool cache_const_expr_analyzer(uchar **arg);
@@ -3079,6 +3117,7 @@ public:
   bool is_result_field() { return false; }
   void save_in_result_field(bool no_conversions);
   Item *get_tmp_table_item(THD *thd);
+  bool find_not_null_fields(table_map allowed);
   bool collect_item_field_processor(void * arg);
   bool add_field_to_set_processor(void * arg);
   bool find_item_in_field_list_processor(void *arg);
@@ -4858,6 +4897,10 @@ public:
   { 
     return depended_from ? 0 : (*ref)->not_null_tables();
   }
+  bool find_not_null_fields(table_map allowed)
+  {
+    return depended_from ? false : (*ref)->find_not_null_fields(allowed);
+  }
   void save_in_result_field(bool no_conversions)
   {
     (*ref)->save_in_field(result_field, no_conversions);
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 49e0a9466c0..c4b43a7459d 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -1192,6 +1192,15 @@ bool Item_in_optimizer::eval_not_null_tables(void *opt_arg)
 }
 
 
+bool Item_in_optimizer::find_not_null_fields(table_map allowed)
+{
+  if (!(~allowed & used_tables()) && is_top_level_item())
+  {
+    return args[0]->find_not_null_fields(allowed);
+  }
+  return false;
+}
+
 void Item_in_optimizer::print(String *str, enum_query_type query_type)
 {
    restore_first_argument();
@@ -2035,7 +2044,17 @@ bool Item_func_between::eval_not_null_tables(void *opt_arg)
                           (args[1]->not_null_tables() &
                            args[2]->not_null_tables()));
   return 0;
-}  
+}
+
+
+bool Item_func_between::find_not_null_fields(table_map allowed)
+{
+  if (negated || !is_top_level_item() || (~allowed & used_tables()))
+    return false;
+  return args[0]->find_not_null_fields(allowed) ||
+         args[1]->find_not_null_fields(allowed) ||
+         args[3]->find_not_null_fields(allowed);
+}
 
 
 bool Item_func_between::count_sargable_conds(void *arg)
@@ -4151,6 +4170,15 @@ Item_func_in::eval_not_null_tables(void *opt_arg)
 }
 
 
+bool
+Item_func_in::find_not_null_fields(table_map allowed)
+{
+  if (negated || !is_top_level_item() || (~allowed & used_tables()))
+    return 0;
+  return  args[0]->find_not_null_fields(allowed);
+}
+
+
 void Item_func_in::fix_after_pullout(st_select_lex *new_parent, Item **ref,
                                      bool merge)
 {
@@ -4705,6 +4733,82 @@ Item_cond::eval_not_null_tables(void *opt_arg)
 }
 
 
+/**
+   @note
+     This implementation of the virtual function find_not_null_fields()
+     infers null-rejectedness if fields from tables marked in 'allowed' from
+     this condition.
+     Currently only top level AND conjuncts that  are not disjunctions are used
+     for the inference. Usage of any top level and-or formula with l OR levels
+     would require a stack of bitmaps for fields of the height h=2*l+1 So we
+     would have to allocate h-1 additional field bitmaps for each table marked
+     in 'allowed'.
+*/
+
+bool
+Item_cond::find_not_null_fields(table_map allowed)
+{
+  Item *item;
+  bool is_and_cond= functype() == Item_func::COND_AND_FUNC;
+  if (!is_and_cond)
+  {
+    /* Now only fields of top AND level conjuncts are taken into account */
+    return false;
+  }
+  uint isnull_func_cnt= 0;
+  List_iterator<Item> li(list);
+  while ((item=li++))
+  {
+    bool is_mult_eq= item->type() == Item::FUNC_ITEM &&
+         ((Item_func *) item)->functype() == Item_func::MULT_EQUAL_FUNC;
+    if (is_mult_eq)
+    {
+      if (!item->find_not_null_fields(allowed))
+        continue;
+    }
+
+    if (~allowed & item->used_tables())
+      continue;
+
+    /* It is assumed that all constant conjuncts are already eliminated */
+
+    /*
+      First infer null-rejectedness of fields from all conjuncts but
+      IS NULL predicates
+    */
+    bool isnull_func= item->type() == Item::FUNC_ITEM &&
+         ((Item_func *) item)->functype() == Item_func::ISNULL_FUNC;
+    if (isnull_func)
+    {
+      isnull_func_cnt++;
+      continue;
+    }
+    if (!item->find_not_null_fields(allowed))
+      continue;
+  }
+
+  /* Now try no get contradictions using IS NULL conjuncts */
+  if (isnull_func_cnt)
+  {
+    li.rewind();
+    while ((item=li++) && isnull_func_cnt)
+    {
+      if (~allowed & item->used_tables())
+        continue;
+
+      bool isnull_func= item->type() == Item::FUNC_ITEM &&
+           ((Item_func *) item)->functype() == Item_func::ISNULL_FUNC;
+      if (isnull_func)
+      {
+        if  (item->find_not_null_fields(allowed))
+          return true;
+        isnull_func_cnt--;
+      }
+    }
+  }
+  return false;
+}
+
 void Item_cond::fix_after_pullout(st_select_lex *new_parent, Item **ref,
                                   bool merge)
 {
@@ -5148,6 +5252,19 @@ longlong Item_func_isnull::val_int()
 }
 
 
+bool Item_func_isnull::find_not_null_fields(table_map allowed)
+{
+  if (!(~allowed & used_tables()) &&
+      args[0]->real_item()->type() == Item::FIELD_ITEM)
+  {
+    Field *field= ((Item_field *)(args[0]->real_item()))->field;
+    if (bitmap_is_set(&field->table->tmp_set, field->field_index))
+      return true;
+  }
+  return false;
+}
+
+
 void Item_func_isnull::print(String *str, enum_query_type query_type)
 {
   if (const_item() && !args[0]->maybe_null &&
@@ -6734,6 +6851,48 @@ void Item_equal::update_used_tables()
 }
 
 
+/**
+  @note
+    This multiple equality can contains elements belonging not to tables {T}
+    marked in 'allowed' . So we can ascertain null-rejectedness of field f
+    belonging to table t from {T} only if one of the following equality
+    predicate can be  extracted from this multiple equality:
+    - f=const
+    - f=f' where f' is a field of some table from {T}
+*/
+
+bool Item_equal::find_not_null_fields(table_map allowed)
+{
+  if (!(allowed & used_tables()))
+    return false;
+  bool checked= false;
+  Item_equal_fields_iterator it(*this);
+  Item *item;
+  while ((item= it++))
+  {
+    if (~allowed & item->used_tables())
+      continue;
+    if ((with_const || checked) && !item->find_not_null_fields(allowed))
+      continue;
+    Item_equal_fields_iterator it1(*this);
+    Item *item1;
+    while ((item1= it1++) && item1 != item)
+    {
+      if (~allowed & item1->used_tables())
+        continue;
+      if (!item->find_not_null_fields(allowed) &&
+          !item1->find_not_null_fields(allowed))
+      {
+        checked= true;
+        break;
+      }
+    }
+  }
+  return false;
+}
+
+
+
 bool Item_equal::count_sargable_conds(void *arg)
 {
   SELECT_LEX *sel= (SELECT_LEX *) arg;
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 7d99cbdb936..8f01e4fa13f 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -284,6 +284,7 @@ public:
     Item_func_truth(thd, a, true, false) {}
   ~Item_func_isnottrue() {}
   virtual const char* func_name() const { return "isnottrue"; }
+  bool find_not_null_fields(table_map allowed) { return false; }
   Item *get_copy(THD *thd)
   { return get_item_copy<Item_func_isnottrue>(thd, this); }
 };
@@ -315,6 +316,7 @@ public:
     Item_func_truth(thd, a, false, false) {}
   ~Item_func_isnotfalse() {}
   virtual const char* func_name() const { return "isnotfalse"; }
+  bool find_not_null_fields(table_map allowed) { return false; }
   Item *get_copy(THD *thd)
   { return get_item_copy<Item_func_isnotfalse>(thd, this); }
 };
@@ -376,6 +378,7 @@ public:
   virtual void get_cache_parameters(List<Item> &parameters);
   bool is_top_level_item();
   bool eval_not_null_tables(void *opt_arg);
+  bool find_not_null_fields(table_map allowed);
   void fix_after_pullout(st_select_lex *new_parent, Item **ref, bool merge);
   bool invisible_mode();
   void reset_cache() { cache= NULL; }
@@ -571,6 +574,7 @@ public:
   void print(String *str, enum_query_type query_type)
   { Item_func::print_op(str, query_type); }
   longlong val_int();
+  bool find_not_null_fields(table_map allowed) { return false; }
   Item *neg_transformer(THD *thd);
   Item* propagate_equal_fields(THD *thd, const Context &ctx, COND_EQUAL *cond)
   {
@@ -592,6 +596,7 @@ public:
   longlong val_int();
   enum Functype functype() const { return NOT_FUNC; }
   const char *func_name() const { return "not"; }
+  bool find_not_null_fields(table_map allowed) { return false; }
   enum precedence precedence() const { return BANG_PRECEDENCE; }
   Item *neg_transformer(THD *thd);
   bool fix_fields(THD *, Item **);
@@ -736,6 +741,7 @@ public:
   longlong val_int();
   bool fix_length_and_dec();
   table_map not_null_tables() const { return 0; }
+  bool find_not_null_fields(table_map allowed) { return false; }
   enum Functype functype() const { return EQUAL_FUNC; }
   enum Functype rev_functype() const { return EQUAL_FUNC; }
   cond_result eq_cmp_result() const { return COND_TRUE; }
@@ -912,6 +918,7 @@ public:
   bool fix_length_and_dec_numeric(THD *);
   virtual void print(String *str, enum_query_type query_type);
   bool eval_not_null_tables(void *opt_arg);
+  bool find_not_null_fields(table_map allowed);
   void fix_after_pullout(st_select_lex *new_parent, Item **ref, bool merge);
   bool count_sargable_conds(void *arg);
   void add_key_fields(JOIN *join, KEY_FIELD **key_fields,
@@ -2396,6 +2403,7 @@ public:
   const char *func_name() const { return "in"; }
   enum precedence precedence() const { return CMP_PRECEDENCE; }
   bool eval_not_null_tables(void *opt_arg);
+  bool find_not_null_fields(table_map allowed);
   void fix_after_pullout(st_select_lex *new_parent, Item **ref, bool merge);
   bool count_sargable_conds(void *arg);
   Item *get_copy(THD *thd)
@@ -2533,6 +2541,7 @@ public:
   COND *remove_eq_conds(THD *thd, Item::cond_result *cond_value,
                         bool top_level);
   table_map not_null_tables() const { return 0; }
+  bool find_not_null_fields(table_map allowed);
   Item *neg_transformer(THD *thd);
   Item *get_copy(THD *thd)
   { return get_item_copy<Item_func_isnull>(thd, this); }
@@ -2953,6 +2962,7 @@ public:
   Item *compile(THD *thd, Item_analyzer analyzer, uchar **arg_p,
                 Item_transformer transformer, uchar *arg_t);
   bool eval_not_null_tables(void *opt_arg);
+  bool find_not_null_fields(table_map allowed);
   Item *build_clone(THD *thd);
   bool excl_dep_on_table(table_map tab_map);
   bool excl_dep_on_grouping_fields(st_select_lex *sel);
@@ -3119,6 +3129,7 @@ public:
     eval_item= NULL;
   }
   void update_used_tables();
+  bool find_not_null_fields(table_map allowed);
   COND *build_equal_items(THD *thd, COND_EQUAL *inherited,
                           bool link_item_fields,
                           COND_EQUAL **cond_equal_ref);
diff --git a/sql/item_func.cc b/sql/item_func.cc
index a030d2f8681..41bbd2ea8f0 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -403,6 +403,25 @@ Item_func::eval_not_null_tables(void *opt_arg)
 }
 
 
+bool
+Item_func::find_not_null_fields(table_map allowed)
+{
+  if (~allowed & used_tables())
+    return false;
+
+  Item **arg,**arg_end;
+  if (arg_count)
+  {
+    for (arg=args, arg_end=args+arg_count; arg != arg_end ; arg++)
+    {
+      if (!(*arg)->find_not_null_fields(allowed))
+        continue;
+    }
+  }
+  return false;
+}
+
+
 void Item_func::fix_after_pullout(st_select_lex *new_parent, Item **ref,
                                   bool merge)
 {
diff --git a/sql/item_func.h b/sql/item_func.h
index 6345dd41f71..325a79600e9 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -210,6 +210,7 @@ public:
   void traverse_cond(Cond_traverser traverser,
                      void * arg, traverse_order order);
   bool eval_not_null_tables(void *opt_arg);
+  bool find_not_null_fields(table_map allowed);
  // bool is_expensive_processor(void *arg);
  // virtual bool is_expensive() { return 0; }
   inline void raise_numeric_overflow(const char *type_name)
@@ -671,6 +672,7 @@ public:
   Item_func_case_expression(THD *thd, List<Item> &list):
     Item_func_hybrid_field_type(thd, list)
   { }
+  bool find_not_null_fields(table_map allowed) { return false; }
 };
 
 
@@ -1759,6 +1761,10 @@ public:
     not_null_tables_cache= 0;
     return false;
   }
+  bool find_not_null_fields(table_map allowed)
+  {
+    return false;
+  }
   Item* propagate_equal_fields(THD *thd, const Context &ctx, COND_EQUAL *cond)
   { return this; }
   bool const_item() const { return true; }
@@ -2119,6 +2125,10 @@ public:
     not_null_tables_cache= 0;
     return 0;
   }
+  bool find_not_null_fields(table_map allowed)
+  {
+    return false;
+  }
   bool is_expensive() { return 1; }
   virtual void print(String *str, enum_query_type query_type);
   bool check_vcol_func_processor(void *arg)
@@ -2711,6 +2721,10 @@ public:
     not_null_tables_cache= 0;
     return 0;
   }
+  bool find_not_null_fields(table_map allowed)
+  {
+    return false;
+  }
   bool fix_fields(THD *thd, Item **ref);
   bool eq(const Item *, bool binary_cmp) const;
   /* The following should be safe, even if we compare doubles */
@@ -3000,6 +3014,10 @@ public:
     not_null_tables_cache= 0;
     return 0;
   }
+  bool find_not_null_fields(table_map allowed)
+  {
+    return false;
+  }
 };
 
 
@@ -3103,6 +3121,10 @@ public:
     not_null_tables_cache= 0;
     return 0;
   }
+  bool find_not_null_fields(table_map allowed)
+  {
+    return false;
+  }
   bool const_item() const { return 0; }
   void evaluate_sideeffects();
   void update_used_tables()
diff --git a/sql/item_row.cc b/sql/item_row.cc
index 70a9fe50c16..0b55ae5d022 100644
--- a/sql/item_row.cc
+++ b/sql/item_row.cc
@@ -87,6 +87,25 @@ Item_row::eval_not_null_tables(void *opt_arg)
 }
 
 
+bool
+Item_row::find_not_null_fields(table_map allowed)
+{
+  if (~allowed & used_tables())
+    return false;
+
+  Item **arg,**arg_end;
+  if (arg_count)
+  {
+    for (arg= args, arg_end= args + arg_count; arg != arg_end ; arg++)
+    {
+      if (!(*arg)->find_not_null_fields(allowed))
+        continue;
+    }
+  }
+  return false;
+}
+
+
 void Item_row::cleanup()
 {
   DBUG_ENTER("Item_row::cleanup");
diff --git a/sql/item_row.h b/sql/item_row.h
index 0d6a6db2e0e..0efa29f3d79 100644
--- a/sql/item_row.h
+++ b/sql/item_row.h
@@ -110,6 +110,7 @@ public:
   }
   Item *transform(THD *thd, Item_transformer transformer, uchar *arg);
   bool eval_not_null_tables(void *opt_arg);
+  bool find_not_null_fields(table_map allowed);
 
   uint cols() const { return arg_count; }
   Item* element_index(uint i) { return args[i]; }
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index ec7b3dbbd7a..91103a5cf34 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -2399,6 +2399,7 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use,
 {
   uint idx;
   double scan_time;
+  Item *notnull_cond= NULL;
   DBUG_ENTER("SQL_SELECT::test_quick_select");
   DBUG_PRINT("enter",("keys_to_use: %lu  prev_tables: %lu  const_tables: %lu",
 		      (ulong) keys_to_use.to_ulonglong(), (ulong) prev_tables,
@@ -2412,6 +2413,7 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use,
   if (keys_to_use.is_clear_all() || head->is_filled_at_execution())
     DBUG_RETURN(0);
   records= head->stat_records();
+  notnull_cond= head->notnull_cond;
   if (!records)
     records++;					/* purecov: inspected */
   scan_time= (double) records / TIME_FOR_COMPARE + 1;
@@ -2419,7 +2421,10 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use,
   if (head->force_index)
     scan_time= read_time= DBL_MAX;
   if (limit < records)
+  {
     read_time= (double) records + scan_time + 1; // Force to use index
+    notnull_cond= NULL;
+  }
   
   possible_keys.clear_all();
 
@@ -2431,6 +2436,7 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use,
     uchar buff[STACK_BUFF_ALLOC];
     MEM_ROOT alloc;
     SEL_TREE *tree= NULL;
+    SEL_TREE *notnull_cond_tree= NULL;
     KEY_PART *key_parts;
     KEY *key_info;
     PARAM param;
@@ -2539,6 +2545,9 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use,
     TRP_GROUP_MIN_MAX *group_trp;
     double best_read_time= read_time;
 
+    if (notnull_cond)
+      notnull_cond_tree= notnull_cond->get_mm_tree(&param, &notnull_cond);
+
     if (cond)
     {
       if ((tree= cond->get_mm_tree(&param, &cond)))
@@ -2557,6 +2566,7 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use,
           tree= NULL;
       }
     }
+    tree= tree_and(&param, tree, notnull_cond_tree);
 
     /*
       Try to construct a QUICK_GROUP_MIN_MAX_SELECT.
diff --git a/sql/sql_priv.h b/sql/sql_priv.h
index 2f37e1106f0..ec7b2ccbdac 100644
--- a/sql/sql_priv.h
+++ b/sql/sql_priv.h
@@ -222,6 +222,7 @@
 #define OPTIMIZER_SWITCH_ORDERBY_EQ_PROP           (1ULL << 29)
 #define OPTIMIZER_SWITCH_COND_PUSHDOWN_FOR_DERIVED (1ULL << 30)
 #define OPTIMIZER_SWITCH_SPLIT_MATERIALIZED        (1ULL << 31)
+#define OPTIMIZER_SWITCH_NOT_NULL_RANGE_SCAN       (1ULL << 32)
 
 #define OPTIMIZER_SWITCH_DEFAULT   (OPTIMIZER_SWITCH_INDEX_MERGE | \
                                     OPTIMIZER_SWITCH_INDEX_MERGE_UNION | \
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 0fbc4bac7d8..dae2cd76308 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -295,6 +295,14 @@ void set_postjoin_aggr_write_func(JOIN_TAB *tab);
 
 static Item **get_sargable_cond(JOIN *join, TABLE *table);
 
+static
+bool build_notnull_conds_for_range_scans(JOIN *join, COND *cond,
+                                         table_map allowed);
+static
+void build_notnull_conds_for_inner_nest_of_outer_join(JOIN *join,
+                                                      TABLE_LIST *nest_tbl);
+
+
 #ifndef DBUG_OFF
 
 /*
@@ -4907,6 +4915,9 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list,
     }
   }
 
+  join->join_tab= stat;
+  join->make_notnull_conds_for_range_scans();
+
   /* Calc how many (possible) matched records in each table */
 
   for (s=stat ; s < stat_end ; s++)
@@ -27572,6 +27583,280 @@ Item *remove_pushed_top_conjuncts(THD *thd, Item *cond)
   return cond;
 }
 
+
+/**
+  @brief
+  Construct not null conditions for provingly not nullable fields
+
+  @details
+    For each non-constant joined table the function creates a conjunction
+    of IS NOT NULL predicates containing a predicate for each field used
+    in the WHERE clause or an OR expression such that
+      - is declared as nullable
+      - for which it can proved be that it is null-rejected
+      - is a part of some index.
+    This conjunction could be anded with either the WHERE condition or with
+    an ON expression and the modified join query would produce the same
+    result set as the original one.
+    If a conjunction of IS NOT NULL predicates is constructed for an inner
+    table of an outer join OJ that is not an inner table of embedded outer
+    joins then it is to be anded with the ON expression of OJ.
+    The constructed conjunctions of IS NOT NULL predicates  are attached
+    to the corresponding tables. They used for range analysis complementary
+    to other sargable range conditions.
+
+  @note
+    Let f be a field of the joined table t. In the context of the upper
+    paragraph field f is called null-rejected if any the following holds:
+
+    - t is a table of a top inner join and a conjunctive formula that rejects
+      rows with null values for f can be extracted from the WHERE condition
+
+    - t is an outer table of a top outer join operation and a conjunctive
+      formula over the outer tables of the outer join that rejects rows with
+      null values for can be extracted from the WHERE condition
+
+    - t is an outer table of a non-top outer join operation and a conjunctive
+      formula over the outer tables of the outer join that rejects rows with
+      null values for f can be extracted from the ON expression of the
+      embedding outer join
+
+    - the joined table is an inner table of a outer join operation and
+      a conjunctive formula over inner tables of the outer join that rejects
+      rows with null values for f can be extracted from the ON expression of
+      the outer join operation.
+
+    It is assumed above that all inner join nests have been eliminated and
+    that all possible conversions of outer joins into inner joins have been
+    already done.
+*/
+
+void JOIN::make_notnull_conds_for_range_scans()
+{
+  DBUG_ENTER("JOIN::make_notnull_conds_for_range_scans");
+
+
+  if (impossible_where ||
+      !optimizer_flag(thd, OPTIMIZER_SWITCH_NOT_NULL_RANGE_SCAN))
+  {
+    /* Complementary range analysis is not needed */
+    DBUG_VOID_RETURN;
+  }
+
+  if (conds && build_notnull_conds_for_range_scans(this, conds,
+                                                   conds->used_tables()))
+  {
+    Item *false_cond= new (thd->mem_root) Item_int(thd, (longlong) 0, 1);
+    if (false_cond)
+    {
+      /*
+        Found a IS NULL conjunctive predicate for a null-rejected field
+        in the WHERE clause
+      */
+      conds= false_cond;
+      cond_equal= 0;
+      impossible_where= true;
+    }
+    DBUG_VOID_RETURN;
+  }
+
+  List_iterator<TABLE_LIST> li(*join_list);
+  TABLE_LIST *tbl;
+  while ((tbl= li++))
+  {
+    if (tbl->on_expr)
+    {
+      if (tbl->nested_join)
+      {
+        build_notnull_conds_for_inner_nest_of_outer_join(this, tbl);
+      }
+      else if (build_notnull_conds_for_range_scans(this, tbl->on_expr,
+                                                   tbl->table->map))
+      {
+        /*
+          Found a IS NULL conjunctive predicate for a null-rejected field
+          of the inner table of an outer join with ON expression tbl->on_expr
+        */
+        Item *false_cond= new (thd->mem_root) Item_int(thd, (longlong) 0, 1);
+        if (false_cond)
+          tbl->on_expr= false_cond;
+      }
+    }
+  }
+  DBUG_VOID_RETURN;
+}
+
+
+/**
+  @brief
+  Build not null conditions for range scans of given join tables
+
+  @param join    the join for whose tables not null conditions are to be built
+  @param cond    the condition from which not null predicates are to be inferred
+  @param allowed the bit map of join tables to be taken into account
+
+  @details
+    For each join table t from the 'allowed' set of tables the function finds
+    all fields whose null-rejectedness can be inferred from null-rejectedness
+    of the condition cond. For each found field f from table t such that it
+    participates at least in one index on table t a NOT NULL predicate is
+    constructed and a conjunction of all such predicates is attached to t.
+    If when looking for null-rejecting fields of t it is discovered one of its
+    fields has to be null-rejected and there is IS NULL conjunctive top level
+    predicate for this field then the function immediately returns true.
+    The function uses the bitmap TABLE::tmp_set to mark found null-rejected
+    fields of table t.
+
+  @note
+    Currently only top level conjuncts without disjunctive sub-formulas are
+    are taken into account when looking for null-rejected fields.
+
+  @retval
+    true    if a contradiction is inferred
+    false   otherwise
+*/
+
+static
+bool build_notnull_conds_for_range_scans(JOIN *join, Item *cond,
+                                         table_map allowed)
+{
+  THD *thd= join->thd;
+
+  DBUG_ENTER("build_notnull_conds_for_range_scans");
+
+  for (JOIN_TAB *s= join->join_tab + join->const_tables ;
+       s < join->join_tab + join->table_count ; s++)
+  {
+    /* Clear all needed bitmaps to mark found fields */
+    if (allowed & s->table->map)
+      bitmap_clear_all(&s->table->tmp_set);
+  }
+
+  /*
+    Find all null-rejected fields assuming that cond is null-rejected and
+    only  formulas over tables from 'allowed' are to be taken into account
+  */
+  if (cond->find_not_null_fields(allowed))
+    DBUG_RETURN(true);
+
+  /*
+    For each table t from 'allowed' build a conjunction of NOT NULL predicates
+    constructed for all found fields if they are included in some indexes.
+    If the construction of the conjunction succeeds attach the formula to
+    t->table->notnull_cond. The condition will be used to look for complementary
+    range scans.
+  */
+  for (JOIN_TAB *s= join->join_tab + join->const_tables ;
+       s < join->join_tab + join->table_count ; s++)
+  {
+    TABLE *tab= s->table;
+    List<Item> notnull_list;
+    Item *notnull_cond= 0;
+
+    if (!(allowed & tab->map))
+      continue;
+
+    for (Field** field_ptr= tab->field; *field_ptr; field_ptr++)
+    {
+      Field *field= *field_ptr;
+      if (field->part_of_key.is_clear_all())
+        continue;
+      if (!bitmap_is_set(&tab->tmp_set, field->field_index))
+        continue;
+      Item_field *field_item= new (thd->mem_root) Item_field(thd, field);
+      if (!field_item)
+        continue;
+      Item *isnotnull_item=
+         new (thd->mem_root) Item_func_isnotnull(thd, field_item);
+      if (!isnotnull_item)
+        continue;
+      if (notnull_list.push_back(isnotnull_item, thd->mem_root))
+        continue;
+      s->const_keys.merge(field->part_of_key);
+    }
+
+    switch (notnull_list.elements) {
+    case 0:
+      break;
+    case 1:
+      notnull_cond= notnull_list.head();
+      break;
+    default:
+      notnull_cond=
+        new (thd->mem_root) Item_cond_and(thd, notnull_list);
+    }
+    if (notnull_cond && !notnull_cond->fix_fields(thd, 0))
+    {
+      tab->notnull_cond= notnull_cond;
+    }
+  }
+  DBUG_RETURN(false);
+}
+
+
+/**
+  @brief
+  Build not null conditions for inner nest tables of an outer join
+
+  @param join  the join for whose table nest not null conditions are to be built
+  @param nest_tbl the nest of the inner tables of an outer join
+
+  @details
+    The function assumes that nest_tbl is the nest of the inner tables of an
+    outer join and so an ON expression for this outer join is attached to
+    nest_tbl.
+    The function selects the tables of the nest_tbl that are not inner tables of
+    embedded outer joins and then it calls build_notnull_conds_for_range_scans()
+    for nest_tbl->on_expr and the bitmap for the selected tables. This call
+    finds all fields belonging to the selected tables whose null-rejectedness
+    can be inferred from the null-rejectedness of nest_tbl->on_expr. After this
+    the function recursively finds all null_rejected fields for the remaining
+    tables from the nest of nest_tbl.
+*/
+
+static
+void build_notnull_conds_for_inner_nest_of_outer_join(JOIN *join,
+                                                      TABLE_LIST *nest_tbl)
+{
+  TABLE_LIST *tbl;
+  table_map used_tables= 0;
+  THD *thd= join->thd;
+  List_iterator<TABLE_LIST> li(nest_tbl->nested_join->join_list);
+
+  while ((tbl= li++))
+  {
+    if (!tbl->on_expr)
+      used_tables|= tbl->table->map;
+  }
+  if (used_tables &&
+      build_notnull_conds_for_range_scans(join, nest_tbl->on_expr, used_tables))
+  {
+    Item *false_cond= new (thd->mem_root) Item_int(thd, (longlong) 0, 1);
+    if (false_cond)
+      nest_tbl->on_expr= false_cond;
+  }
+
+  li.rewind();
+  while ((tbl= li++))
+  {
+    if (tbl->on_expr)
+    {
+      if (tbl->nested_join)
+      {
+        build_notnull_conds_for_inner_nest_of_outer_join(join, tbl);
+      }
+      else if (build_notnull_conds_for_range_scans(join, tbl->on_expr,
+                                                   tbl->table->map))
+      {
+        Item *false_cond= new (thd->mem_root) Item_int(thd, (longlong) 0, 1);
+        if (false_cond)
+          tbl->on_expr= false_cond;
+      }
+    }
+  }
+}
+
+
 /**
   @} (end of group Query_Optimizer)
 */
diff --git a/sql/sql_select.h b/sql/sql_select.h
index dd823a7d7df..2b40a414d57 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -1749,6 +1749,7 @@ public:
   void add_keyuses_for_splitting();
   bool inject_best_splitting_cond(table_map remaining_tables);
   bool fix_all_splittings_in_plan();
+  void make_notnull_conds_for_range_scans();
 
   bool transform_in_predicates_into_in_subq(THD *thd);
 private:
@@ -2343,7 +2344,7 @@ Item_equal *find_item_equal(COND_EQUAL *cond_equal, Field *field,
 extern bool test_if_ref(Item *, 
                  Item_field *left_item,Item *right_item);
 
-inline bool optimizer_flag(THD *thd, uint flag)
+inline bool optimizer_flag(THD *thd, ulonglong flag)
 { 
   return (thd->variables.optimizer_switch & flag);
 }
diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc
index b50f697b489..58b6da53e6c 100644
--- a/sql/sys_vars.cc
+++ b/sql/sys_vars.cc
@@ -2544,6 +2544,7 @@ export const char *optimizer_switch_names[]=
   "orderby_uses_equalities",
   "condition_pushdown_for_derived",
   "split_materialized",
+  "not_null_range_scan",
   "default", 
   NullS
 };
diff --git a/sql/table.cc b/sql/table.cc
index 4805017972c..34fb6d5feac 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -4658,6 +4658,8 @@ void TABLE::init(THD *thd, TABLE_LIST *tl)
     (*f_ptr)->cond_selectivity= 1.0;
   }
 
+  notnull_cond= 0;
+
   DBUG_ASSERT(!file->keyread_enabled());
 
   restore_record(this, s->default_values);
diff --git a/sql/table.h b/sql/table.h
index fa6cb707efb..47096aacb67 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -1344,6 +1344,13 @@ public:
   SplM_opt_info *spl_opt_info;
   key_map keys_usable_for_splitting;
 
+  /*
+    Conjunction of the predicates of the form IS NOT NULL(f) where f refers to
+    a column of this TABLE such that they can be inferred from the condition
+    of the  WHERE clause or from some ON expression of the processed select
+    and can be useful for range optimizer.
+  */
+  Item *notnull_cond;
 
   inline void reset() { bzero((void*)this, sizeof(*this)); }
   void init(THD *thd, TABLE_LIST *tl);
diff --git a/storage/tokudb/mysql-test/tokudb/r/ext_key_1_innodb.result b/storage/tokudb/mysql-test/tokudb/r/ext_key_1_innodb.result
index a80e1664663..5d471d2228d 100644
--- a/storage/tokudb/mysql-test/tokudb/r/ext_key_1_innodb.result
+++ b/storage/tokudb/mysql-test/tokudb/r/ext_key_1_innodb.result
@@ -1,7 +1,7 @@
 drop table if exists t;
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,not_null_range_scan=off
 create table t (id int not null, x int not null, y int not null, primary key(id), key(x)) engine=innodb;
 insert into t values (0,0,0),(1,1,1),(2,2,2),(3,2,3),(4,2,4);
 explain select x,id from t force index (x) where x=0 and id=0;
diff --git a/storage/tokudb/mysql-test/tokudb/r/ext_key_1_tokudb.result b/storage/tokudb/mysql-test/tokudb/r/ext_key_1_tokudb.result
index 96d681407fe..a3c518afa6a 100644
--- a/storage/tokudb/mysql-test/tokudb/r/ext_key_1_tokudb.result
+++ b/storage/tokudb/mysql-test/tokudb/r/ext_key_1_tokudb.result
@@ -1,7 +1,7 @@
 drop table if exists t;
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,not_null_range_scan=off
 create table t (id int not null, x int not null, y int not null, primary key(id), key(x)) engine=tokudb;
 insert into t values (0,0,0),(1,1,1),(2,2,2),(3,2,3),(4,2,4);
 explain select x,id from t force index (x) where x=0 and id=0;
diff --git a/storage/tokudb/mysql-test/tokudb/r/ext_key_2_innodb.result b/storage/tokudb/mysql-test/tokudb/r/ext_key_2_innodb.result
index 43737c7753e..21dadb8b6c3 100644
--- a/storage/tokudb/mysql-test/tokudb/r/ext_key_2_innodb.result
+++ b/storage/tokudb/mysql-test/tokudb/r/ext_key_2_innodb.result
@@ -1,7 +1,7 @@
 drop table if exists t;
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,not_null_range_scan=off
 create table t (a int not null, b int not null, c int not null, d int not null, primary key(a,b), key(c,a)) engine=innodb;
 insert into t values (0,0,0,0),(0,1,0,1);
 explain select c,a,b from t where c=0 and a=0 and b=1;
diff --git a/storage/tokudb/mysql-test/tokudb/r/ext_key_2_tokudb.result b/storage/tokudb/mysql-test/tokudb/r/ext_key_2_tokudb.result
index 1dcb1ee1b8b..41c3f2bbc11 100644
--- a/storage/tokudb/mysql-test/tokudb/r/ext_key_2_tokudb.result
+++ b/storage/tokudb/mysql-test/tokudb/r/ext_key_2_tokudb.result
@@ -1,7 +1,7 @@
 drop table if exists t;
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,not_null_range_scan=off
 create table t (a int not null, b int not null, c int not null, d int not null, primary key(a,b), key(c,a)) engine=tokudb;
 insert into t values (0,0,0,0),(0,1,0,1);
 explain select c,a,b from t where c=0 and a=0 and b=1;