This is a full cost-based implementation of the optimization that employs

splitting technique for equi-joins of materialized derived tables/views/CTEs.
(see mdev-13369 and mdev-13389).

23 files changed, 2507 insertions, 596 deletions

diff --git a/libmysqld/CMakeLists.txt b/libmysqld/CMakeLists.txt
index 19962df0bec..6e717267548 100644
--- a/libmysqld/CMakeLists.txt
+++ b/libmysqld/CMakeLists.txt
@@ -119,6 +119,7 @@ SET(SQL_EMBEDDED_SOURCES emb_qcache.cc libmysqld.c lib_sql.cc
            ../sql/session_tracker.cc
            ../sql/proxy_protocol.cc
            ../sql/sql_tvc.cc ../sql/sql_tvc.h
+           ../sql/opt_split.cc
            ${GEN_SOURCES}
            ${MYSYS_LIBWRAP_SOURCE}
 )
diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result
index 41c8c51a09b..cc6cc190dea 100644
--- a/mysql-test/r/derived_cond_pushdown.result
+++ b/mysql-test/r/derived_cond_pushdown.result
@@ -12543,7 +12543,7 @@ drop table t1;
 # MDEV-10855: Pushdown into derived with window functions 
 #
 set @save_optimizer_switch= @@optimizer_switch;
-set optimizer_switch='split_grouping_derived=off';
+set optimizer_switch='split_materialized=off';
 create table t1 (a int, c varchar(16));
 insert into t1 values
 (8,'aa'), (5,'cc'), (1,'bb'), (2,'aa'), (9,'cc'),
@@ -13244,59 +13244,76 @@ set optimizer_switch= @save_optimizer_switch;
 # MDEV-13389: Optimization for equi-joins of derived tables with WF
 #             (Splitting derived tables / views with window functions)
 #
-create table t1 (a int);
+create table t1 (a int, b int, index idx_b(b)) engine=myisam;
 insert into t1 values
-(8), (5), (1), (2), (9), (7), (2), (7);
-create table t2 (a int, b int, index idx(a));
+(8,3), (5,7), (1,2), (2,1), (9,7), (7,5), (2,2), (7,3),
+(9,3), (8,1), (4,5), (2,3);
+create table t2 (a int, b int, c char(127), index idx_a(a)) engine=myisam;
 insert into t2 values
-(7,10), (1,20), (2,23), (7,18), (1,30),
-(4,71), (3,15), (7,82), (8,12), (4,15),
-(11,33), (10,42), (4,53), (10,17), (2,90);
-set statement optimizer_switch='split_grouping_derived=off' for select t1.a,t.max,t.min
+(7,10,'x'), (1,20,'a'), (2,23,'b'), (7,18,'z'), (1,30,'c'),
+(4,71,'d'), (3,15,'x'), (7,82,'y'), (8,12,'t'), (4,15,'b'),
+(11,33,'a'), (10,42,'u'), (4,53,'p'), (10,17,'r'), (2,90,'x'),
+(17,10,'s'), (11,20,'v'), (12,23,'y'), (17,18,'a'), (11,30,'d'),
+(24,71,'h'), (23,15,'i'), (27,82,'k'), (28,12,'p'), (24,15,'q'),
+(31,33,'f'), (30,42,'h'), (40,53,'m'), (30,17,'o'), (21,90,'b'),
+(37,10,'e'), (31,20,'g'), (32,23,'f'), (37,18,'n'), (41,30,'l'),
+(54,71,'j'), (53,15,'w'), (57,82,'z'), (58,12,'k'), (54,15,'p'),
+(61,33,'c'), (60,42,'a'), (62,53,'x'), (67,17,'g'), (64,90,'v');
+insert into t2 select a+10, b+10, concat(c,'f') from t2;
+analyze table t1,t2;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+test.t2	analyze	status	OK
+set statement optimizer_switch='split_materialized=off' for select t1.a,t.s,t.m
 from t1 join
-(select a, max(t2.b) max, min(t2.b) min from t2 group by t2.a) t
-on t1.a=t.a;
-a	max	min
-8	12	12
-1	30	20
-2	90	23
-7	82	10
-2	90	23
-7	82	10
-select t1.a,t.max,t.min
+(select a, sum(t2.b) as s, min(t2.c) as m from t2 group by t2.a) t
+on t1.a=t.a
+where t1.b < 3;
+a	s	m
+2	113	b
+8	12	t
+1	50	a
+2	113	b
+select t1.a,t.s,t.m
 from t1 join
-(select a, max(t2.b) max, min(t2.b) min from t2 group by t2.a) t
-on t1.a=t.a;
-a	max	min
-8	12	12
-1	30	20
-2	90	23
-7	82	10
-2	90	23
-7	82	10
-explain extended select t1.a,t.max,t.min
+(select a, sum(t2.b) as s, min(t2.c) as m from t2 group by t2.a) t
+on t1.a=t.a
+where t1.b < 3;
+a	s	m
+2	113	b
+8	12	t
+1	50	a
+2	113	b
+explain extended select t1.a,t.s,t.m
 from t1 join
-(select a, max(t2.b) max, min(t2.b) min from t2 group by t2.a) t
-on t1.a=t.a;
+(select a, sum(t2.b) as s, min(t2.c) as m from t2 group by t2.a) t
+on t1.a=t.a
+where t1.b < 3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	100.00	Using where
+1	PRIMARY	t1	range	idx_b	idx_b	5	NULL	4	100.00	Using index condition; Using where
 1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.a	2	100.00	
-2	LATERAL DERIVED	t2	ref	idx	idx	5	test.t1.a	2	100.00	
+2	LATERAL DERIVED	t2	ref	idx_a	idx_a	5	test.t1.a	2	100.00	
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`t`.`max` AS `max`,`t`.`min` AS `min` from `test`.`t1` join (/* select#2 */ select `test`.`t2`.`a` AS `a`,max(`test`.`t2`.`b`) AS `max`,min(`test`.`t2`.`b`) AS `min` from `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`a` group by `test`.`t2`.`a`) `t` where `t`.`a` = `test`.`t1`.`a`
-explain format=json select t1.a,t.max,t.min
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`t`.`s` AS `s`,`t`.`m` AS `m` from `test`.`t1` join (/* select#2 */ select `test`.`t2`.`a` AS `a`,sum(`test`.`t2`.`b`) AS `s`,min(`test`.`t2`.`c`) AS `m` from `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`a` group by `test`.`t2`.`a`) `t` where `t`.`a` = `test`.`t1`.`a` and `test`.`t1`.`b` < 3
+explain format=json select t1.a,t.s,t.m
 from t1 join
-(select a, max(t2.b) max, min(t2.b) min from t2 group by t2.a) t
-on t1.a=t.a;
+(select a, sum(t2.b) as s, min(t2.c) as m from t2 group by t2.a) t
+on t1.a=t.a
+where t1.b < 3;
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
     "table": {
       "table_name": "t1",
-      "access_type": "ALL",
-      "rows": 8,
+      "access_type": "range",
+      "possible_keys": ["idx_b"],
+      "key": "idx_b",
+      "key_length": "5",
+      "used_key_parts": ["b"],
+      "rows": 4,
       "filtered": 100,
+      "index_condition": "t1.b < 3",
       "attached_condition": "t1.a is not null"
     },
     "table": {
@@ -13316,8 +13333,8 @@ EXPLAIN
           "table": {
             "table_name": "t2",
             "access_type": "ref",
-            "possible_keys": ["idx"],
-            "key": "idx",
+            "possible_keys": ["idx_a"],
+            "key": "idx_a",
             "key_length": "5",
             "used_key_parts": ["a"],
             "ref": ["test.t1.a"],
@@ -13329,40 +13346,167 @@ EXPLAIN
     }
   }
 }
-set statement optimizer_switch='split_grouping_derived=off' for select t1.a,t.max,t.min
+prepare stmt from "select t1.a,t.s,t.m
+from t1 join
+(select a, sum(t2.b) as s, min(t2.c) as m from t2 group by t2.a) t
+on t1.a=t.a
+where t1.b < 3";
+execute stmt;
+a	s	m
+2	113	b
+8	12	t
+1	50	a
+2	113	b
+execute stmt;
+a	s	m
+2	113	b
+8	12	t
+1	50	a
+2	113	b
+deallocate prepare stmt;
+set statement optimizer_switch='split_materialized=off' for select t1.a,t.s,t.m
+from t1 join
+(select a, sum(t2.b) as s, min(t2.b) as m from t2 group by t2.a) t
+on t1.a=t.a
+where t1.b <= 5;
+a	s	m
+8	12	12
+1	50	20
+2	113	23
+7	110	10
+2	113	23
+7	110	10
+8	12	12
+4	139	15
+2	113	23
+select t1.a,t.s,t.m
+from t1 join
+(select a, sum(t2.b) as s, min(t2.b) as m from t2 group by t2.a) t
+on t1.a=t.a
+where t1.b <= 5;
+a	s	m
+8	12	12
+1	50	20
+2	113	23
+7	110	10
+2	113	23
+7	110	10
+8	12	12
+4	139	15
+2	113	23
+explain extended select t1.a,t.s,t.m
+from t1 join
+(select a, sum(t2.b) as s, min(t2.b) as m from t2 group by t2.a) t
+on t1.a=t.a
+where t1.b <= 5;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	idx_b	NULL	NULL	NULL	12	75.00	Using where
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.a	9	100.00	
+2	DERIVED	t2	ALL	idx_a	NULL	NULL	NULL	90	100.00	Using temporary; Using filesort
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`t`.`s` AS `s`,`t`.`m` AS `m` from `test`.`t1` join (/* select#2 */ select `test`.`t2`.`a` AS `a`,sum(`test`.`t2`.`b`) AS `s`,min(`test`.`t2`.`b`) AS `m` from `test`.`t2` group by `test`.`t2`.`a`) `t` where `t`.`a` = `test`.`t1`.`a` and `test`.`t1`.`b` <= 5
+explain format=json select t1.a,t.s,t.m
+from t1 join
+(select a, sum(t2.b) as s, min(t2.b) as m from t2 group by t2.a) t
+on t1.a=t.a
+where t1.b <= 5;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t1",
+      "access_type": "ALL",
+      "possible_keys": ["idx_b"],
+      "rows": 12,
+      "filtered": 75,
+      "attached_condition": "t1.b <= 5 and t1.a is not null"
+    },
+    "table": {
+      "table_name": "<derived2>",
+      "access_type": "ref",
+      "possible_keys": ["key0"],
+      "key": "key0",
+      "key_length": "5",
+      "used_key_parts": ["a"],
+      "ref": ["test.t1.a"],
+      "rows": 9,
+      "filtered": 100,
+      "materialized": {
+        "query_block": {
+          "select_id": 2,
+          "filesort": {
+            "sort_key": "t2.a",
+            "temporary_table": {
+              "table": {
+                "table_name": "t2",
+                "access_type": "ALL",
+                "possible_keys": ["idx_a"],
+                "rows": 90,
+                "filtered": 100
+              }
+            }
+          }
+        }
+      }
+    }
+  }
+}
+prepare stmt from "select t1.a,t.s,t.m
+from t1 join
+(select a, sum(t2.b) as s, min(t2.b) as m from t2 group by t2.a) t
+on t1.a=t.a
+where t1.b <= 5";
+execute stmt;
+a	s	m
+8	12	12
+1	50	20
+2	113	23
+7	110	10
+2	113	23
+7	110	10
+8	12	12
+4	139	15
+2	113	23
+execute stmt;
+a	s	m
+8	12	12
+1	50	20
+2	113	23
+7	110	10
+2	113	23
+7	110	10
+8	12	12
+4	139	15
+2	113	23
+deallocate prepare stmt;
+delete from t1 where t1.b between 2 and 5;
+set statement optimizer_switch='split_materialized=off' for select t1.a,t.max,t.min
 from t1 left join
 (select a, max(t2.b) max, min(t2.b) min from t2 group by t2.a) t
 on t1.a=t.a;
 a	max	min
-8	12	12
 5	NULL	NULL
-1	30	20
 2	90	23
 9	NULL	NULL
-7	82	10
-2	90	23
-7	82	10
+8	12	12
 select t1.a,t.max,t.min
 from t1 left join
 (select a, max(t2.b) max, min(t2.b) min from t2 group by t2.a) t
 on t1.a=t.a;
 a	max	min
-8	12	12
 5	NULL	NULL
-1	30	20
 2	90	23
 9	NULL	NULL
-7	82	10
-2	90	23
-7	82	10
+8	12	12
 explain extended select t1.a,t.max,t.min
 from t1 left join
 (select a, max(t2.b) max, min(t2.b) min from t2 group by t2.a) t
 on t1.a=t.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	100.00	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	100.00	
 1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.a	2	100.00	Using where
-2	LATERAL DERIVED	t2	ref	idx	idx	5	test.t1.a	2	100.00	
+2	LATERAL DERIVED	t2	ref	idx_a	idx_a	5	test.t1.a	2	100.00	
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`t`.`max` AS `max`,`t`.`min` AS `min` from `test`.`t1` left join (/* select#2 */ select `test`.`t2`.`a` AS `a`,max(`test`.`t2`.`b`) AS `max`,min(`test`.`t2`.`b`) AS `min` from `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`a` group by `test`.`t2`.`a`) `t` on(`t`.`a` = `test`.`t1`.`a` and `test`.`t1`.`a` is not null) where 1
 explain format=json select t1.a,t.max,t.min
@@ -13377,7 +13521,7 @@ EXPLAIN
     "table": {
       "table_name": "t1",
       "access_type": "ALL",
-      "rows": 8,
+      "rows": 4,
       "filtered": 100
     },
     "table": {
@@ -13398,8 +13542,8 @@ EXPLAIN
           "table": {
             "table_name": "t2",
             "access_type": "ref",
-            "possible_keys": ["idx"],
-            "key": "idx",
+            "possible_keys": ["idx_a"],
+            "key": "idx_a",
             "key_length": "5",
             "used_key_parts": ["a"],
             "ref": ["test.t1.a"],
@@ -13411,53 +13555,68 @@ EXPLAIN
     }
   }
 }
-create table t3 (a int, c varchar(16));
+create table t3 (a int, b int, c char(127), index idx_b(b)) engine=myisam;
 insert into t3 values
-(8,'aa'), (5,'cc'), (1,'bb'), (2,'aa'), (9,'cc'),
-(7,'aa'), (2,'aa'), (7,'bb');
-create table t4 (a int, b int, c varchar(16), index idx(a,c));
+(8,11,'aa'), (5,15,'cc'), (1,14,'bb'), (2,12,'aa'), (7,17,'cc'),
+(7,18,'aa'), (2,11,'aa'), (7,10,'bb'), (3,11,'dd'), (4,12,'ee'),
+(5,14,'dd'), (9,12,'ee');
+create table t4 (a int, b int, c char(127), index idx(a,c)) engine=myisam;
 insert into t4 values
 (7,10,'cc'), (1,20,'aa'), (2,23,'bb'), (7,18,'cc'), (1,30,'bb'),
-(4,71,'xx'), (3,15,'aa'), (7,82,'bb'), (8,12,'dd'), (4,15,'aa'),
-(11,33,'yy'), (10,42,'zz'), (4,53,'xx'), (10,17,'yy'), (7,12,'bb'),
+(4,71,'xx'), (3,15,'aa'), (7,82,'aa'), (8,12,'dd'), (4,15,'aa'),
+(11,33,'yy'), (10,42,'zz'), (4,53,'xx'), (10,17,'yy'), (7,12,'cc'),
 (8,20,'dd'), (7,32,'bb'), (1,50,'aa'), (3,40,'bb'), (3,77,'aa');
-set statement optimizer_switch='split_grouping_derived=off' for select t3.a,t3.c,t.max,t.min
+insert into t4 select a+10, b+10, concat(c,'f') from t4;
+analyze table t3,t4;
+Table	Op	Msg_type	Msg_text
+test.t3	analyze	status	OK
+test.t4	analyze	status	OK
+set statement optimizer_switch='split_materialized=off' for select t3.a,t3.c,t.max,t.min
 from t3 join
 (select a, c, max(b) max, min(b) min from t4 group by a,c) t
-on t3.a=t.a and t3.c=t.c;
+on t3.a=t.a and t3.c=t.c
+where t3.b > 15;
 a	c	max	min
-1	bb	30	30
-7	bb	82	12
+7	cc	18	10
+7	aa	82	82
 select t3.a,t3.c,t.max,t.min
 from t3 join
 (select a, c, max(b) max, min(b) min from t4 group by a,c) t
-on t3.a=t.a and t3.c=t.c;
+on t3.a=t.a and t3.c=t.c
+where t3.b > 15;
 a	c	max	min
-1	bb	30	30
-7	bb	82	12
+7	cc	18	10
+7	aa	82	82
 explain extended select t3.a,t3.c,t.max,t.min
 from t3 join
 (select a, c, max(b) max, min(b) min from t4 group by a,c) t
-on t3.a=t.a and t3.c=t.c;
+on t3.a=t.a and t3.c=t.c
+where t3.b > 15;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	8	100.00	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	24	test.t3.a,test.t3.c	2	100.00	
-2	LATERAL DERIVED	t4	ref	idx	idx	24	test.t3.a,test.t3.c	2	100.00	
+1	PRIMARY	t3	range	idx_b	idx_b	5	NULL	3	100.00	Using index condition; Using where
+1	PRIMARY	<derived2>	ref	key0	key0	133	test.t3.a,test.t3.c	2	100.00	
+2	LATERAL DERIVED	t4	ref	idx	idx	133	test.t3.a,test.t3.c	1	100.00	
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`c` AS `c`,`t`.`max` AS `max`,`t`.`min` AS `min` from `test`.`t3` join (/* select#2 */ select `test`.`t4`.`a` AS `a`,`test`.`t4`.`c` AS `c`,max(`test`.`t4`.`b`) AS `max`,min(`test`.`t4`.`b`) AS `min` from `test`.`t4` where `test`.`t4`.`a` = `test`.`t3`.`a` and `test`.`t4`.`c` = `test`.`t3`.`c` group by `test`.`t4`.`a`,`test`.`t4`.`c`) `t` where `t`.`a` = `test`.`t3`.`a` and `t`.`c` = `test`.`t3`.`c`
+Note	1003	/* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`c` AS `c`,`t`.`max` AS `max`,`t`.`min` AS `min` from `test`.`t3` join (/* select#2 */ select `test`.`t4`.`a` AS `a`,`test`.`t4`.`c` AS `c`,max(`test`.`t4`.`b`) AS `max`,min(`test`.`t4`.`b`) AS `min` from `test`.`t4` where `test`.`t4`.`a` = `test`.`t3`.`a` and `test`.`t4`.`c` = `test`.`t3`.`c` group by `test`.`t4`.`a`,`test`.`t4`.`c`) `t` where `t`.`a` = `test`.`t3`.`a` and `t`.`c` = `test`.`t3`.`c` and `test`.`t3`.`b` > 15
 explain format=json select t3.a,t3.c,t.max,t.min
 from t3 join
 (select a, c, max(b) max, min(b) min from t4 group by a,c) t
-on t3.a=t.a and t3.c=t.c;
+on t3.a=t.a and t3.c=t.c
+where t3.b > 15;
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
     "table": {
       "table_name": "t3",
-      "access_type": "ALL",
-      "rows": 8,
+      "access_type": "range",
+      "possible_keys": ["idx_b"],
+      "key": "idx_b",
+      "key_length": "5",
+      "used_key_parts": ["b"],
+      "rows": 3,
       "filtered": 100,
+      "index_condition": "t3.b > 15",
       "attached_condition": "t3.a is not null and t3.c is not null"
     },
     "table": {
@@ -13465,7 +13624,7 @@ EXPLAIN
       "access_type": "ref",
       "possible_keys": ["key0"],
       "key": "key0",
-      "key_length": "24",
+      "key_length": "133",
       "used_key_parts": ["a", "c"],
       "ref": ["test.t3.a", "test.t3.c"],
       "rows": 2,
@@ -13479,10 +13638,10 @@ EXPLAIN
             "access_type": "ref",
             "possible_keys": ["idx"],
             "key": "idx",
-            "key_length": "24",
+            "key_length": "133",
             "used_key_parts": ["a", "c"],
             "ref": ["test.t3.a", "test.t3.c"],
-            "rows": 2,
+            "rows": 1,
             "filtered": 100
           }
         }
@@ -13490,43 +13649,126 @@ EXPLAIN
     }
   }
 }
-set statement optimizer_switch='split_grouping_derived=off' for select t3.a,t3.c,t.max,t.min
+set statement optimizer_switch='split_materialized=off' for select t3.a,t3.c,t.max,t.min
 from t3 join
-(select a, c, max(b) max, min(b) min from t4 group by c,a) t
-on t3.a=t.a and t3.c=t.c;
+(select a, c, max(b) max, min(b) min from t4 group by a,c) t
+on t3.a=t.a and t3.c=t.c
+where t3.b <= 15;
 a	c	max	min
 1	bb	30	30
-7	bb	82	12
+7	bb	32	32
 select t3.a,t3.c,t.max,t.min
 from t3 join
-(select a, c, max(b) max, min(b) min from t4 group by c,a) t
-on t3.a=t.a and t3.c=t.c;
+(select a, c, max(b) max, min(b) min from t4 group by a,c) t
+on t3.a=t.a and t3.c=t.c
+where t3.b <= 15;
 a	c	max	min
 1	bb	30	30
-7	bb	82	12
+7	bb	32	32
+explain extended select t3.a,t3.c,t.max,t.min
+from t3 join
+(select a, c, max(b) max, min(b) min from t4 group by a,c) t
+on t3.a=t.a and t3.c=t.c
+where t3.b <= 15;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t3	ALL	idx_b	NULL	NULL	NULL	12	75.00	Using where
+1	PRIMARY	<derived2>	ref	key0	key0	133	test.t3.a,test.t3.c	4	100.00	
+2	DERIVED	t4	ALL	idx	NULL	NULL	NULL	40	100.00	Using temporary; Using filesort
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`c` AS `c`,`t`.`max` AS `max`,`t`.`min` AS `min` from `test`.`t3` join (/* select#2 */ select `test`.`t4`.`a` AS `a`,`test`.`t4`.`c` AS `c`,max(`test`.`t4`.`b`) AS `max`,min(`test`.`t4`.`b`) AS `min` from `test`.`t4` group by `test`.`t4`.`a`,`test`.`t4`.`c`) `t` where `t`.`a` = `test`.`t3`.`a` and `t`.`c` = `test`.`t3`.`c` and `test`.`t3`.`b` <= 15
+explain format=json select t3.a,t3.c,t.max,t.min
+from t3 join
+(select a, c, max(b) max, min(b) min from t4 group by a,c) t
+on t3.a=t.a and t3.c=t.c
+where t3.b <= 15;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t3",
+      "access_type": "ALL",
+      "possible_keys": ["idx_b"],
+      "rows": 12,
+      "filtered": 75,
+      "attached_condition": "t3.b <= 15 and t3.a is not null and t3.c is not null"
+    },
+    "table": {
+      "table_name": "<derived2>",
+      "access_type": "ref",
+      "possible_keys": ["key0"],
+      "key": "key0",
+      "key_length": "133",
+      "used_key_parts": ["a", "c"],
+      "ref": ["test.t3.a", "test.t3.c"],
+      "rows": 4,
+      "filtered": 100,
+      "materialized": {
+        "query_block": {
+          "select_id": 2,
+          "filesort": {
+            "sort_key": "t4.a, t4.c",
+            "temporary_table": {
+              "table": {
+                "table_name": "t4",
+                "access_type": "ALL",
+                "possible_keys": ["idx"],
+                "rows": 40,
+                "filtered": 100
+              }
+            }
+          }
+        }
+      }
+    }
+  }
+}
+set statement optimizer_switch='split_materialized=off' for select t3.a,t3.c,t.max,t.min
+from t3 join
+(select a, c, max(b) max, min(b) min from t4 group by c,a) t
+on t3.a=t.a and t3.c=t.c
+where t3.b > 15;
+a	c	max	min
+7	cc	18	10
+7	aa	82	82
+select t3.a,t3.c,t.max,t.min
+from t3 join
+(select a, c, max(b) max, min(b) min from t4 group by c,a) t
+on t3.a=t.a and t3.c=t.c
+where t3.b > 15;
+a	c	max	min
+7	cc	18	10
+7	aa	82	82
 explain extended select t3.a,t3.c,t.max,t.min
 from t3 join
 (select a, c, max(b) max, min(b) min from t4 group by c,a) t
-on t3.a=t.a and t3.c=t.c;
+on t3.a=t.a and t3.c=t.c
+where t3.b > 15;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	8	100.00	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	24	test.t3.a,test.t3.c	2	100.00	
-2	LATERAL DERIVED	t4	ref	idx	idx	24	test.t3.a,test.t3.c	2	100.00	
+1	PRIMARY	t3	range	idx_b	idx_b	5	NULL	3	100.00	Using index condition; Using where
+1	PRIMARY	<derived2>	ref	key0	key0	133	test.t3.a,test.t3.c	2	100.00	
+2	LATERAL DERIVED	t4	ref	idx	idx	133	test.t3.a,test.t3.c	1	100.00	
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`c` AS `c`,`t`.`max` AS `max`,`t`.`min` AS `min` from `test`.`t3` join (/* select#2 */ select `test`.`t4`.`a` AS `a`,`test`.`t4`.`c` AS `c`,max(`test`.`t4`.`b`) AS `max`,min(`test`.`t4`.`b`) AS `min` from `test`.`t4` where `test`.`t4`.`c` = `test`.`t3`.`c` and `test`.`t4`.`a` = `test`.`t3`.`a` group by `test`.`t4`.`c`,`test`.`t4`.`a`) `t` where `t`.`a` = `test`.`t3`.`a` and `t`.`c` = `test`.`t3`.`c`
+Note	1003	/* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`c` AS `c`,`t`.`max` AS `max`,`t`.`min` AS `min` from `test`.`t3` join (/* select#2 */ select `test`.`t4`.`a` AS `a`,`test`.`t4`.`c` AS `c`,max(`test`.`t4`.`b`) AS `max`,min(`test`.`t4`.`b`) AS `min` from `test`.`t4` where `test`.`t4`.`a` = `test`.`t3`.`a` and `test`.`t4`.`c` = `test`.`t3`.`c` group by `test`.`t4`.`c`,`test`.`t4`.`a`) `t` where `t`.`a` = `test`.`t3`.`a` and `t`.`c` = `test`.`t3`.`c` and `test`.`t3`.`b` > 15
 explain format=json select t3.a,t3.c,t.max,t.min
 from t3 join
 (select a, c, max(b) max, min(b) min from t4 group by c,a) t
-on t3.a=t.a and t3.c=t.c;
+on t3.a=t.a and t3.c=t.c
+where t3.b > 15;
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
     "table": {
       "table_name": "t3",
-      "access_type": "ALL",
-      "rows": 8,
+      "access_type": "range",
+      "possible_keys": ["idx_b"],
+      "key": "idx_b",
+      "key_length": "5",
+      "used_key_parts": ["b"],
+      "rows": 3,
       "filtered": 100,
+      "index_condition": "t3.b > 15",
       "attached_condition": "t3.a is not null and t3.c is not null"
     },
     "table": {
@@ -13534,7 +13776,7 @@ EXPLAIN
       "access_type": "ref",
       "possible_keys": ["key0"],
       "key": "key0",
-      "key_length": "24",
+      "key_length": "133",
       "used_key_parts": ["a", "c"],
       "ref": ["test.t3.a", "test.t3.c"],
       "rows": 2,
@@ -13548,10 +13790,10 @@ EXPLAIN
             "access_type": "ref",
             "possible_keys": ["idx"],
             "key": "idx",
-            "key_length": "24",
+            "key_length": "133",
             "used_key_parts": ["a", "c"],
             "ref": ["test.t3.a", "test.t3.c"],
-            "rows": 2,
+            "rows": 1,
             "filtered": 100
           }
         }
@@ -13559,44 +13801,120 @@ EXPLAIN
     }
   }
 }
-drop index idx on t2;
-create index idx on t2(b);
-create index idx on t3(a);
-create index idx2 on t4(c);
-insert into t3 select a+1, concat(c,'f') from t3;
-insert into t3 select a+1, concat(c,'h')  from t3;
-insert into t4 select a+1, b+10, concat(c,'h') from t4;
-set statement optimizer_switch='split_grouping_derived=off' for select t2.a,t2.b,t3.c,t.max,t.min
+set statement optimizer_switch='split_materialized=off' for select t3.a,t3.c,t.max,t.min
+from t3 join
+(select a, c, max(b) max, min(b) min from t4 group by c,a) t
+on t3.a=t.a and t3.c=t.c
+where t3.b <= 15;
+a	c	max	min
+1	bb	30	30
+7	bb	32	32
+select t3.a,t3.c,t.max,t.min
+from t3 join
+(select a, c, max(b) max, min(b) min from t4 group by c,a) t
+on t3.a=t.a and t3.c=t.c
+where t3.b <= 15;
+a	c	max	min
+1	bb	30	30
+7	bb	32	32
+explain extended select t3.a,t3.c,t.max,t.min
+from t3 join
+(select a, c, max(b) max, min(b) min from t4 group by c,a) t
+on t3.a=t.a and t3.c=t.c
+where t3.b <= 15;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t3	ALL	idx_b	NULL	NULL	NULL	12	75.00	Using where
+1	PRIMARY	<derived2>	ref	key0	key0	133	test.t3.a,test.t3.c	4	100.00	
+2	DERIVED	t4	ALL	idx	NULL	NULL	NULL	40	100.00	Using temporary; Using filesort
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`c` AS `c`,`t`.`max` AS `max`,`t`.`min` AS `min` from `test`.`t3` join (/* select#2 */ select `test`.`t4`.`a` AS `a`,`test`.`t4`.`c` AS `c`,max(`test`.`t4`.`b`) AS `max`,min(`test`.`t4`.`b`) AS `min` from `test`.`t4` group by `test`.`t4`.`c`,`test`.`t4`.`a`) `t` where `t`.`a` = `test`.`t3`.`a` and `t`.`c` = `test`.`t3`.`c` and `test`.`t3`.`b` <= 15
+explain format=json select t3.a,t3.c,t.max,t.min
+from t3 join
+(select a, c, max(b) max, min(b) min from t4 group by c,a) t
+on t3.a=t.a and t3.c=t.c
+where t3.b <= 15;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t3",
+      "access_type": "ALL",
+      "possible_keys": ["idx_b"],
+      "rows": 12,
+      "filtered": 75,
+      "attached_condition": "t3.b <= 15 and t3.a is not null and t3.c is not null"
+    },
+    "table": {
+      "table_name": "<derived2>",
+      "access_type": "ref",
+      "possible_keys": ["key0"],
+      "key": "key0",
+      "key_length": "133",
+      "used_key_parts": ["a", "c"],
+      "ref": ["test.t3.a", "test.t3.c"],
+      "rows": 4,
+      "filtered": 100,
+      "materialized": {
+        "query_block": {
+          "select_id": 2,
+          "filesort": {
+            "sort_key": "t4.c, t4.a",
+            "temporary_table": {
+              "table": {
+                "table_name": "t4",
+                "access_type": "ALL",
+                "possible_keys": ["idx"],
+                "rows": 40,
+                "filtered": 100
+              }
+            }
+          }
+        }
+      }
+    }
+  }
+}
+drop index idx_a on t2;
+create index idx on t2(c,b);
+create index idx_a on t3(a);
+create index idx_c on t4(c);
+insert into t3 select a+10, b+10, concat(c,'f') from t3;
+insert into t3 select a+100, b+100, concat(c,'g')  from t3;
+insert into t4 select a+100, b+100, concat(c,'g') from t4;
+insert into t4 select a+1000, b+1000, concat(c,'h') from t4;
+analyze table t2,t3,t4;
+Table	Op	Msg_type	Msg_text
+test.t2	analyze	status	OK
+test.t3	analyze	status	OK
+test.t4	analyze	status	OK
+set statement optimizer_switch='split_materialized=off' for select t2.a,t2.b,t2.c,t.c as t_c,t.max,t.min
 from t2, t3, (select c, max(b) max, min(b) min from t4 group by c) t
-where t2.b > 50 and t2.a=t3.a and t3.c=t.c;
-a	b	c	max	min
-7	82	aa	77	15
-7	82	bb	82	12
-2	90	aa	77	15
-2	90	aa	77	15
-2	90	bbh	92	22
-select t2.a,t2.b,t3.c,t.max,t.min
+where t2.b between 80 and 85 and t2.c in ('y','z') and t2.a=t3.a and t3.c=t.c;
+a	b	c	t_c	max	min
+7	82	y	cc	18	10
+7	82	y	aa	82	15
+7	82	y	bb	40	23
+select t2.a,t2.b,t2.c,t.c as t_c,t.max,t.min
 from t2, t3, (select c, max(b) max, min(b) min from t4 group by c) t
-where t2.b > 50 and t2.a=t3.a and t3.c=t.c;
-a	b	c	max	min
-7	82	aa	77	15
-7	82	bb	82	12
-2	90	aa	77	15
-2	90	aa	77	15
-2	90	bbh	92	22
-explain extended select t2.a,t2.b,t3.c,t.max,t.min
+where t2.b between 80 and 85 and t2.c in ('y','z') and t2.a=t3.a and t3.c=t.c;
+a	b	c	t_c	max	min
+7	82	y	cc	18	10
+7	82	y	aa	82	15
+7	82	y	bb	40	23
+explain extended select t2.a,t2.b,t2.c,t.c as t_c,t.max,t.min
 from t2, t3, (select c, max(b) max, min(b) min from t4 group by c) t
-where t2.b > 50 and t2.a=t3.a and t3.c=t.c;
+where t2.b between 80 and 85 and t2.c in ('y','z') and t2.a=t3.a and t3.c=t.c;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t2	range	idx	idx	5	NULL	5	100.00	Using index condition; Using where
-1	PRIMARY	t3	ref	idx	idx	5	test.t2.a	3	100.00	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	19	test.t3.c	4	100.00	
-2	LATERAL DERIVED	t4	ref	idx2	idx2	19	test.t3.c	5	100.00	
+1	PRIMARY	t2	range	idx	idx	133	NULL	2	100.00	Using index condition; Using where
+1	PRIMARY	t3	ref	idx_a	idx_a	5	test.t2.a	2	100.00	Using where
+1	PRIMARY	<derived2>	ref	key0	key0	128	test.t3.c	2	100.00	
+2	LATERAL DERIVED	t4	ref	idx_c	idx_c	128	test.t3.c	3	100.00	
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`t`.`max` AS `max`,`t`.`min` AS `min` from `test`.`t2` join `test`.`t3` join (/* select#2 */ select `test`.`t4`.`c` AS `c`,max(`test`.`t4`.`b`) AS `max`,min(`test`.`t4`.`b`) AS `min` from `test`.`t4` where `test`.`t4`.`c` = `test`.`t3`.`c` group by `test`.`t4`.`c`) `t` where `test`.`t3`.`a` = `test`.`t2`.`a` and `t`.`c` = `test`.`t3`.`c` and `test`.`t2`.`b` > 50
-explain format=json select t2.a,t2.b,t3.c,t.max,t.min
+Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`t`.`c` AS `t_c`,`t`.`max` AS `max`,`t`.`min` AS `min` from `test`.`t2` join `test`.`t3` join (/* select#2 */ select `test`.`t4`.`c` AS `c`,max(`test`.`t4`.`b`) AS `max`,min(`test`.`t4`.`b`) AS `min` from `test`.`t4` where `test`.`t4`.`c` = `test`.`t3`.`c` group by `test`.`t4`.`c`) `t` where `test`.`t3`.`a` = `test`.`t2`.`a` and `t`.`c` = `test`.`t3`.`c` and `test`.`t2`.`b` between 80 and 85 and `test`.`t2`.`c` in ('y','z')
+explain format=json select t2.a,t2.b,t2.c,t.c as t_c,t.max,t.min
 from t2, t3, (select c, max(b) max, min(b) min from t4 group by c) t
-where t2.b > 50 and t2.a=t3.a and t3.c=t.c;
+where t2.b between 80 and 85 and t2.c in ('y','z') and t2.a=t3.a and t3.c=t.c;
 EXPLAIN
 {
   "query_block": {
@@ -13606,22 +13924,22 @@ EXPLAIN
       "access_type": "range",
       "possible_keys": ["idx"],
       "key": "idx",
-      "key_length": "5",
-      "used_key_parts": ["b"],
-      "rows": 5,
+      "key_length": "133",
+      "used_key_parts": ["c", "b"],
+      "rows": 2,
       "filtered": 100,
-      "index_condition": "t2.b > 50",
+      "index_condition": "t2.b between 80 and 85 and t2.c in ('y','z')",
       "attached_condition": "t2.a is not null"
     },
     "table": {
       "table_name": "t3",
       "access_type": "ref",
-      "possible_keys": ["idx"],
-      "key": "idx",
+      "possible_keys": ["idx_a"],
+      "key": "idx_a",
       "key_length": "5",
       "used_key_parts": ["a"],
       "ref": ["test.t2.a"],
-      "rows": 3,
+      "rows": 2,
       "filtered": 100,
       "attached_condition": "t3.c is not null"
     },
@@ -13630,10 +13948,10 @@ EXPLAIN
       "access_type": "ref",
       "possible_keys": ["key0"],
       "key": "key0",
-      "key_length": "19",
+      "key_length": "128",
       "used_key_parts": ["c"],
       "ref": ["test.t3.c"],
-      "rows": 4,
+      "rows": 2,
       "filtered": 100,
       "materialized": {
         "query_block": {
@@ -13642,12 +13960,12 @@ EXPLAIN
           "table": {
             "table_name": "t4",
             "access_type": "ref",
-            "possible_keys": ["idx2"],
-            "key": "idx2",
-            "key_length": "19",
+            "possible_keys": ["idx_c"],
+            "key": "idx_c",
+            "key_length": "128",
             "used_key_parts": ["c"],
             "ref": ["test.t3.c"],
-            "rows": 5,
+            "rows": 3,
             "filtered": 100
           }
         }
@@ -13655,81 +13973,195 @@ EXPLAIN
     }
   }
 }
-set statement optimizer_switch='split_grouping_derived=off' for select *
+set statement optimizer_switch='split_materialized=off' for select t2.a,t2.b,t2.c,t.c as t_c,t.max,t.min
+from t2, t3, (select c, max(b) max, min(b) min from t4 group by c) t
+where t2.b < 40 and t2.a=t3.a and t3.c=t.c;
+a	b	c	t_c	max	min
+7	10	x	cc	18	10
+7	10	x	aa	82	15
+7	10	x	bb	40	23
+1	20	a	bb	40	23
+2	23	b	aa	82	15
+2	23	b	aa	82	15
+7	18	z	cc	18	10
+7	18	z	aa	82	15
+7	18	z	bb	40	23
+1	30	c	bb	40	23
+3	15	x	dd	20	12
+8	12	t	aa	82	15
+11	33	a	bbf	50	33
+17	10	s	ccf	28	20
+17	10	s	aaf	92	25
+17	10	s	bbf	50	33
+11	20	v	bbf	50	33
+12	23	y	aaf	92	25
+12	23	y	aaf	92	25
+17	18	a	ccf	28	20
+17	18	a	aaf	92	25
+17	18	a	bbf	50	33
+11	30	d	bbf	50	33
+17	20	xf	ccf	28	20
+17	20	xf	aaf	92	25
+17	20	xf	bbf	50	33
+11	30	af	bbf	50	33
+12	33	bf	aaf	92	25
+12	33	bf	aaf	92	25
+17	28	zf	ccf	28	20
+17	28	zf	aaf	92	25
+17	28	zf	bbf	50	33
+13	25	xf	ddf	30	22
+18	22	tf	aaf	92	25
+select t2.a,t2.b,t2.c,t.c as t_c,t.max,t.min
+from t2, t3, (select c, max(b) max, min(b) min from t4 group by c) t
+where t2.b < 40 and t2.a=t3.a and t3.c=t.c;
+a	b	c	t_c	max	min
+7	10	x	cc	18	10
+7	10	x	aa	82	15
+7	10	x	bb	40	23
+1	20	a	bb	40	23
+2	23	b	aa	82	15
+2	23	b	aa	82	15
+7	18	z	cc	18	10
+7	18	z	aa	82	15
+7	18	z	bb	40	23
+1	30	c	bb	40	23
+3	15	x	dd	20	12
+8	12	t	aa	82	15
+11	33	a	bbf	50	33
+17	10	s	ccf	28	20
+17	10	s	aaf	92	25
+17	10	s	bbf	50	33
+11	20	v	bbf	50	33
+12	23	y	aaf	92	25
+12	23	y	aaf	92	25
+17	18	a	ccf	28	20
+17	18	a	aaf	92	25
+17	18	a	bbf	50	33
+11	30	d	bbf	50	33
+17	20	xf	ccf	28	20
+17	20	xf	aaf	92	25
+17	20	xf	bbf	50	33
+11	30	af	bbf	50	33
+12	33	bf	aaf	92	25
+12	33	bf	aaf	92	25
+17	28	zf	ccf	28	20
+17	28	zf	aaf	92	25
+17	28	zf	bbf	50	33
+13	25	xf	ddf	30	22
+18	22	tf	aaf	92	25
+explain extended select t2.a,t2.b,t2.c,t.c as t_c,t.max,t.min
+from t2, t3, (select c, max(b) max, min(b) min from t4 group by c) t
+where t2.b < 40 and t2.a=t3.a and t3.c=t.c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	90	100.00	Using where
+1	PRIMARY	t3	ref	idx_a	idx_a	5	test.t2.a	2	100.00	Using where
+1	PRIMARY	<derived2>	ref	key0	key0	128	test.t3.c	10	100.00	
+2	DERIVED	t4	ALL	idx_c	NULL	NULL	NULL	160	100.00	Using temporary; Using filesort
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`t`.`c` AS `t_c`,`t`.`max` AS `max`,`t`.`min` AS `min` from `test`.`t2` join `test`.`t3` join (/* select#2 */ select `test`.`t4`.`c` AS `c`,max(`test`.`t4`.`b`) AS `max`,min(`test`.`t4`.`b`) AS `min` from `test`.`t4` group by `test`.`t4`.`c`) `t` where `test`.`t3`.`a` = `test`.`t2`.`a` and `t`.`c` = `test`.`t3`.`c` and `test`.`t2`.`b` < 40
+explain format=json select t2.a,t2.b,t2.c,t.c as t_c,t.max,t.min
+from t2, t3, (select c, max(b) max, min(b) min from t4 group by c) t
+where t2.b < 40 and t2.a=t3.a and t3.c=t.c;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t2",
+      "access_type": "ALL",
+      "rows": 90,
+      "filtered": 100,
+      "attached_condition": "t2.b < 40 and t2.a is not null"
+    },
+    "table": {
+      "table_name": "t3",
+      "access_type": "ref",
+      "possible_keys": ["idx_a"],
+      "key": "idx_a",
+      "key_length": "5",
+      "used_key_parts": ["a"],
+      "ref": ["test.t2.a"],
+      "rows": 2,
+      "filtered": 100,
+      "attached_condition": "t3.c is not null"
+    },
+    "table": {
+      "table_name": "<derived2>",
+      "access_type": "ref",
+      "possible_keys": ["key0"],
+      "key": "key0",
+      "key_length": "128",
+      "used_key_parts": ["c"],
+      "ref": ["test.t3.c"],
+      "rows": 10,
+      "filtered": 100,
+      "materialized": {
+        "query_block": {
+          "select_id": 2,
+          "filesort": {
+            "sort_key": "t4.c",
+            "temporary_table": {
+              "table": {
+                "table_name": "t4",
+                "access_type": "ALL",
+                "possible_keys": ["idx_c"],
+                "rows": 160,
+                "filtered": 100
+              }
+            }
+          }
+        }
+      }
+    }
+  }
+}
+set statement optimizer_switch='split_materialized=off' for select *
 from t2, t3, (select c, b, sum(b) over (partition by c) from t4 ) t
-where t2.b > 50 and t2.a=t3.a and t3.c=t.c;
-a	b	a	c	c	b	sum(b) over (partition by c)
-7	82	7	aa	aa	77	177
-7	82	7	aa	aa	50	177
-7	82	7	aa	aa	15	177
-7	82	7	aa	aa	15	177
-7	82	7	aa	aa	20	177
-7	82	7	bb	bb	40	219
-7	82	7	bb	bb	32	219
-7	82	7	bb	bb	12	219
-7	82	7	bb	bb	82	219
-7	82	7	bb	bb	30	219
-7	82	7	bb	bb	23	219
-2	90	2	aa	aa	77	177
-2	90	2	aa	aa	50	177
-2	90	2	aa	aa	15	177
-2	90	2	aa	aa	15	177
-2	90	2	aa	aa	20	177
-2	90	2	aa	aa	77	177
-2	90	2	aa	aa	50	177
-2	90	2	aa	aa	15	177
-2	90	2	aa	aa	15	177
-2	90	2	aa	aa	20	177
-2	90	2	bbh	bbh	50	279
-2	90	2	bbh	bbh	42	279
-2	90	2	bbh	bbh	22	279
-2	90	2	bbh	bbh	92	279
-2	90	2	bbh	bbh	40	279
-2	90	2	bbh	bbh	33	279
+where t2.b between 80 and 85 and t2.c in ('y','z') and t2.a=t3.a and t3.c=t.c;
+a	b	c	a	b	c	c	b	sum(b) over (partition by c)
+7	82	y	7	17	cc	cc	12	40
+7	82	y	7	17	cc	cc	18	40
+7	82	y	7	17	cc	cc	10	40
+7	82	y	7	18	aa	aa	77	259
+7	82	y	7	18	aa	aa	50	259
+7	82	y	7	18	aa	aa	15	259
+7	82	y	7	18	aa	aa	82	259
+7	82	y	7	18	aa	aa	15	259
+7	82	y	7	18	aa	aa	20	259
+7	82	y	7	10	bb	bb	40	125
+7	82	y	7	10	bb	bb	32	125
+7	82	y	7	10	bb	bb	30	125
+7	82	y	7	10	bb	bb	23	125
 select *
 from t2, t3, (select c, b, sum(b) over (partition by c) from t4 ) t
-where t2.b > 50 and t2.a=t3.a and t3.c=t.c;
-a	b	a	c	c	b	sum(b) over (partition by c)
-7	82	7	aa	aa	77	177
-7	82	7	aa	aa	50	177
-7	82	7	aa	aa	15	177
-7	82	7	aa	aa	15	177
-7	82	7	aa	aa	20	177
-7	82	7	bb	bb	40	219
-7	82	7	bb	bb	32	219
-7	82	7	bb	bb	12	219
-7	82	7	bb	bb	82	219
-7	82	7	bb	bb	30	219
-7	82	7	bb	bb	23	219
-2	90	2	aa	aa	77	177
-2	90	2	aa	aa	50	177
-2	90	2	aa	aa	15	177
-2	90	2	aa	aa	15	177
-2	90	2	aa	aa	20	177
-2	90	2	aa	aa	77	177
-2	90	2	aa	aa	50	177
-2	90	2	aa	aa	15	177
-2	90	2	aa	aa	15	177
-2	90	2	aa	aa	20	177
-2	90	2	bbh	bbh	50	279
-2	90	2	bbh	bbh	42	279
-2	90	2	bbh	bbh	22	279
-2	90	2	bbh	bbh	92	279
-2	90	2	bbh	bbh	40	279
-2	90	2	bbh	bbh	33	279
+where t2.b between 80 and 85 and t2.c in ('y','z') and t2.a=t3.a and t3.c=t.c;
+a	b	c	a	b	c	c	b	sum(b) over (partition by c)
+7	82	y	7	17	cc	cc	12	40
+7	82	y	7	17	cc	cc	18	40
+7	82	y	7	17	cc	cc	10	40
+7	82	y	7	18	aa	aa	77	259
+7	82	y	7	18	aa	aa	50	259
+7	82	y	7	18	aa	aa	15	259
+7	82	y	7	18	aa	aa	82	259
+7	82	y	7	18	aa	aa	15	259
+7	82	y	7	18	aa	aa	20	259
+7	82	y	7	10	bb	bb	40	125
+7	82	y	7	10	bb	bb	32	125
+7	82	y	7	10	bb	bb	30	125
+7	82	y	7	10	bb	bb	23	125
 explain extended select *
 from t2, t3, (select c, b, sum(b) over (partition by c) from t4 ) t
-where t2.b > 50 and t2.a=t3.a and t3.c=t.c;
+where t2.b between 80 and 85 and t2.c in ('y','z') and t2.a=t3.a and t3.c=t.c;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t2	range	idx	idx	5	NULL	5	100.00	Using index condition; Using where
-1	PRIMARY	t3	ref	idx	idx	5	test.t2.a	3	100.00	Using where
-1	PRIMARY	<derived2>	ref	key0	key0	19	test.t3.c	4	100.00	
-2	LATERAL DERIVED	t4	ref	idx2	idx2	19	test.t3.c	5	100.00	Using temporary
+1	PRIMARY	t2	range	idx	idx	133	NULL	2	100.00	Using index condition; Using where
+1	PRIMARY	t3	ref	idx_a	idx_a	5	test.t2.a	2	100.00	Using where
+1	PRIMARY	<derived2>	ref	key0	key0	128	test.t3.c	2	100.00	
+2	LATERAL DERIVED	t4	ref	idx_c	idx_c	128	test.t3.c	3	100.00	Using temporary
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`c` AS `c`,`t`.`c` AS `c`,`t`.`b` AS `b`,`t`.`sum(b) over (partition by c)` AS `sum(b) over (partition by c)` from `test`.`t2` join `test`.`t3` join (/* select#2 */ select `test`.`t4`.`c` AS `c`,`test`.`t4`.`b` AS `b`,sum(`test`.`t4`.`b`) over ( partition by `test`.`t4`.`c`) AS `sum(b) over (partition by c)` from `test`.`t4` where `test`.`t4`.`c` = `test`.`t3`.`c`) `t` where `test`.`t3`.`a` = `test`.`t2`.`a` and `t`.`c` = `test`.`t3`.`c` and `test`.`t2`.`b` > 50
+Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`t`.`c` AS `c`,`t`.`b` AS `b`,`t`.`sum(b) over (partition by c)` AS `sum(b) over (partition by c)` from `test`.`t2` join `test`.`t3` join (/* select#2 */ select `test`.`t4`.`c` AS `c`,`test`.`t4`.`b` AS `b`,sum(`test`.`t4`.`b`) over ( partition by `test`.`t4`.`c`) AS `sum(b) over (partition by c)` from `test`.`t4` where `test`.`t4`.`c` = `test`.`t3`.`c`) `t` where `test`.`t3`.`a` = `test`.`t2`.`a` and `t`.`c` = `test`.`t3`.`c` and `test`.`t2`.`b` between 80 and 85 and `test`.`t2`.`c` in ('y','z')
 explain format=json select *
 from t2, t3, (select c, b, sum(b) over (partition by c) from t4 ) t
-where t2.b > 50 and t2.a=t3.a and t3.c=t.c;
+where t2.b between 80 and 85 and t2.c in ('y','z') and t2.a=t3.a and t3.c=t.c;
 EXPLAIN
 {
   "query_block": {
@@ -13739,22 +14171,22 @@ EXPLAIN
       "access_type": "range",
       "possible_keys": ["idx"],
       "key": "idx",
-      "key_length": "5",
-      "used_key_parts": ["b"],
-      "rows": 5,
+      "key_length": "133",
+      "used_key_parts": ["c", "b"],
+      "rows": 2,
       "filtered": 100,
-      "index_condition": "t2.b > 50",
+      "index_condition": "t2.b between 80 and 85 and t2.c in ('y','z')",
       "attached_condition": "t2.a is not null"
     },
     "table": {
       "table_name": "t3",
       "access_type": "ref",
-      "possible_keys": ["idx"],
-      "key": "idx",
+      "possible_keys": ["idx_a"],
+      "key": "idx_a",
       "key_length": "5",
       "used_key_parts": ["a"],
       "ref": ["test.t2.a"],
-      "rows": 3,
+      "rows": 2,
       "filtered": 100,
       "attached_condition": "t3.c is not null"
     },
@@ -13763,10 +14195,10 @@ EXPLAIN
       "access_type": "ref",
       "possible_keys": ["key0"],
       "key": "key0",
-      "key_length": "19",
+      "key_length": "128",
       "used_key_parts": ["c"],
       "ref": ["test.t3.c"],
-      "rows": 4,
+      "rows": 2,
       "filtered": 100,
       "materialized": {
         "query_block": {
@@ -13782,12 +14214,398 @@ EXPLAIN
               "table": {
                 "table_name": "t4",
                 "access_type": "ref",
-                "possible_keys": ["idx2"],
-                "key": "idx2",
-                "key_length": "19",
+                "possible_keys": ["idx_c"],
+                "key": "idx_c",
+                "key_length": "128",
                 "used_key_parts": ["c"],
                 "ref": ["test.t3.c"],
-                "rows": 5,
+                "rows": 3,
+                "filtered": 100
+              }
+            }
+          }
+        }
+      }
+    }
+  }
+}
+set statement optimizer_switch='split_materialized=off' for select *
+from t2, t3, (select c, b, sum(b) over (partition by c) from t4 ) t
+where t2.b < 40 and t2.a=t3.a and t3.c=t.c;
+a	b	c	a	b	c	c	b	sum(b) over (partition by c)
+7	10	x	7	17	cc	cc	12	40
+7	10	x	7	17	cc	cc	18	40
+7	10	x	7	17	cc	cc	10	40
+7	10	x	7	18	aa	aa	77	259
+7	10	x	7	18	aa	aa	50	259
+7	10	x	7	18	aa	aa	15	259
+7	10	x	7	18	aa	aa	82	259
+7	10	x	7	18	aa	aa	15	259
+7	10	x	7	18	aa	aa	20	259
+7	10	x	7	10	bb	bb	40	125
+7	10	x	7	10	bb	bb	32	125
+7	10	x	7	10	bb	bb	30	125
+7	10	x	7	10	bb	bb	23	125
+1	20	a	1	14	bb	bb	40	125
+1	20	a	1	14	bb	bb	32	125
+1	20	a	1	14	bb	bb	30	125
+1	20	a	1	14	bb	bb	23	125
+2	23	b	2	12	aa	aa	77	259
+2	23	b	2	12	aa	aa	50	259
+2	23	b	2	12	aa	aa	15	259
+2	23	b	2	12	aa	aa	82	259
+2	23	b	2	12	aa	aa	15	259
+2	23	b	2	12	aa	aa	20	259
+2	23	b	2	11	aa	aa	77	259
+2	23	b	2	11	aa	aa	50	259
+2	23	b	2	11	aa	aa	15	259
+2	23	b	2	11	aa	aa	82	259
+2	23	b	2	11	aa	aa	15	259
+2	23	b	2	11	aa	aa	20	259
+7	18	z	7	17	cc	cc	12	40
+7	18	z	7	17	cc	cc	18	40
+7	18	z	7	17	cc	cc	10	40
+7	18	z	7	18	aa	aa	77	259
+7	18	z	7	18	aa	aa	50	259
+7	18	z	7	18	aa	aa	15	259
+7	18	z	7	18	aa	aa	82	259
+7	18	z	7	18	aa	aa	15	259
+7	18	z	7	18	aa	aa	20	259
+7	18	z	7	10	bb	bb	40	125
+7	18	z	7	10	bb	bb	32	125
+7	18	z	7	10	bb	bb	30	125
+7	18	z	7	10	bb	bb	23	125
+1	30	c	1	14	bb	bb	40	125
+1	30	c	1	14	bb	bb	32	125
+1	30	c	1	14	bb	bb	30	125
+1	30	c	1	14	bb	bb	23	125
+3	15	x	3	11	dd	dd	20	32
+3	15	x	3	11	dd	dd	12	32
+8	12	t	8	11	aa	aa	77	259
+8	12	t	8	11	aa	aa	50	259
+8	12	t	8	11	aa	aa	15	259
+8	12	t	8	11	aa	aa	82	259
+8	12	t	8	11	aa	aa	15	259
+8	12	t	8	11	aa	aa	20	259
+11	33	a	11	24	bbf	bbf	50	165
+11	33	a	11	24	bbf	bbf	42	165
+11	33	a	11	24	bbf	bbf	40	165
+11	33	a	11	24	bbf	bbf	33	165
+17	10	s	17	27	ccf	ccf	22	70
+17	10	s	17	27	ccf	ccf	28	70
+17	10	s	17	27	ccf	ccf	20	70
+17	10	s	17	28	aaf	aaf	87	319
+17	10	s	17	28	aaf	aaf	60	319
+17	10	s	17	28	aaf	aaf	25	319
+17	10	s	17	28	aaf	aaf	92	319
+17	10	s	17	28	aaf	aaf	25	319
+17	10	s	17	28	aaf	aaf	30	319
+17	10	s	17	20	bbf	bbf	50	165
+17	10	s	17	20	bbf	bbf	42	165
+17	10	s	17	20	bbf	bbf	40	165
+17	10	s	17	20	bbf	bbf	33	165
+11	20	v	11	24	bbf	bbf	50	165
+11	20	v	11	24	bbf	bbf	42	165
+11	20	v	11	24	bbf	bbf	40	165
+11	20	v	11	24	bbf	bbf	33	165
+12	23	y	12	22	aaf	aaf	87	319
+12	23	y	12	22	aaf	aaf	60	319
+12	23	y	12	22	aaf	aaf	25	319
+12	23	y	12	22	aaf	aaf	92	319
+12	23	y	12	22	aaf	aaf	25	319
+12	23	y	12	22	aaf	aaf	30	319
+12	23	y	12	21	aaf	aaf	87	319
+12	23	y	12	21	aaf	aaf	60	319
+12	23	y	12	21	aaf	aaf	25	319
+12	23	y	12	21	aaf	aaf	92	319
+12	23	y	12	21	aaf	aaf	25	319
+12	23	y	12	21	aaf	aaf	30	319
+17	18	a	17	27	ccf	ccf	22	70
+17	18	a	17	27	ccf	ccf	28	70
+17	18	a	17	27	ccf	ccf	20	70
+17	18	a	17	28	aaf	aaf	87	319
+17	18	a	17	28	aaf	aaf	60	319
+17	18	a	17	28	aaf	aaf	25	319
+17	18	a	17	28	aaf	aaf	92	319
+17	18	a	17	28	aaf	aaf	25	319
+17	18	a	17	28	aaf	aaf	30	319
+17	18	a	17	20	bbf	bbf	50	165
+17	18	a	17	20	bbf	bbf	42	165
+17	18	a	17	20	bbf	bbf	40	165
+17	18	a	17	20	bbf	bbf	33	165
+11	30	d	11	24	bbf	bbf	50	165
+11	30	d	11	24	bbf	bbf	42	165
+11	30	d	11	24	bbf	bbf	40	165
+11	30	d	11	24	bbf	bbf	33	165
+17	20	xf	17	27	ccf	ccf	22	70
+17	20	xf	17	27	ccf	ccf	28	70
+17	20	xf	17	27	ccf	ccf	20	70
+17	20	xf	17	28	aaf	aaf	87	319
+17	20	xf	17	28	aaf	aaf	60	319
+17	20	xf	17	28	aaf	aaf	25	319
+17	20	xf	17	28	aaf	aaf	92	319
+17	20	xf	17	28	aaf	aaf	25	319
+17	20	xf	17	28	aaf	aaf	30	319
+17	20	xf	17	20	bbf	bbf	50	165
+17	20	xf	17	20	bbf	bbf	42	165
+17	20	xf	17	20	bbf	bbf	40	165
+17	20	xf	17	20	bbf	bbf	33	165
+11	30	af	11	24	bbf	bbf	50	165
+11	30	af	11	24	bbf	bbf	42	165
+11	30	af	11	24	bbf	bbf	40	165
+11	30	af	11	24	bbf	bbf	33	165
+12	33	bf	12	22	aaf	aaf	87	319
+12	33	bf	12	22	aaf	aaf	60	319
+12	33	bf	12	22	aaf	aaf	25	319
+12	33	bf	12	22	aaf	aaf	92	319
+12	33	bf	12	22	aaf	aaf	25	319
+12	33	bf	12	22	aaf	aaf	30	319
+12	33	bf	12	21	aaf	aaf	87	319
+12	33	bf	12	21	aaf	aaf	60	319
+12	33	bf	12	21	aaf	aaf	25	319
+12	33	bf	12	21	aaf	aaf	92	319
+12	33	bf	12	21	aaf	aaf	25	319
+12	33	bf	12	21	aaf	aaf	30	319
+17	28	zf	17	27	ccf	ccf	22	70
+17	28	zf	17	27	ccf	ccf	28	70
+17	28	zf	17	27	ccf	ccf	20	70
+17	28	zf	17	28	aaf	aaf	87	319
+17	28	zf	17	28	aaf	aaf	60	319
+17	28	zf	17	28	aaf	aaf	25	319
+17	28	zf	17	28	aaf	aaf	92	319
+17	28	zf	17	28	aaf	aaf	25	319
+17	28	zf	17	28	aaf	aaf	30	319
+17	28	zf	17	20	bbf	bbf	50	165
+17	28	zf	17	20	bbf	bbf	42	165
+17	28	zf	17	20	bbf	bbf	40	165
+17	28	zf	17	20	bbf	bbf	33	165
+13	25	xf	13	21	ddf	ddf	30	52
+13	25	xf	13	21	ddf	ddf	22	52
+18	22	tf	18	21	aaf	aaf	87	319
+18	22	tf	18	21	aaf	aaf	60	319
+18	22	tf	18	21	aaf	aaf	25	319
+18	22	tf	18	21	aaf	aaf	92	319
+18	22	tf	18	21	aaf	aaf	25	319
+18	22	tf	18	21	aaf	aaf	30	319
+select *
+from t2, t3, (select c, b, sum(b) over (partition by c) from t4 ) t
+where t2.b < 40 and t2.a=t3.a and t3.c=t.c;
+a	b	c	a	b	c	c	b	sum(b) over (partition by c)
+7	10	x	7	17	cc	cc	12	40
+7	10	x	7	17	cc	cc	18	40
+7	10	x	7	17	cc	cc	10	40
+7	10	x	7	18	aa	aa	77	259
+7	10	x	7	18	aa	aa	50	259
+7	10	x	7	18	aa	aa	15	259
+7	10	x	7	18	aa	aa	82	259
+7	10	x	7	18	aa	aa	15	259
+7	10	x	7	18	aa	aa	20	259
+7	10	x	7	10	bb	bb	40	125
+7	10	x	7	10	bb	bb	32	125
+7	10	x	7	10	bb	bb	30	125
+7	10	x	7	10	bb	bb	23	125
+1	20	a	1	14	bb	bb	40	125
+1	20	a	1	14	bb	bb	32	125
+1	20	a	1	14	bb	bb	30	125
+1	20	a	1	14	bb	bb	23	125
+2	23	b	2	12	aa	aa	77	259
+2	23	b	2	12	aa	aa	50	259
+2	23	b	2	12	aa	aa	15	259
+2	23	b	2	12	aa	aa	82	259
+2	23	b	2	12	aa	aa	15	259
+2	23	b	2	12	aa	aa	20	259
+2	23	b	2	11	aa	aa	77	259
+2	23	b	2	11	aa	aa	50	259
+2	23	b	2	11	aa	aa	15	259
+2	23	b	2	11	aa	aa	82	259
+2	23	b	2	11	aa	aa	15	259
+2	23	b	2	11	aa	aa	20	259
+7	18	z	7	17	cc	cc	12	40
+7	18	z	7	17	cc	cc	18	40
+7	18	z	7	17	cc	cc	10	40
+7	18	z	7	18	aa	aa	77	259
+7	18	z	7	18	aa	aa	50	259
+7	18	z	7	18	aa	aa	15	259
+7	18	z	7	18	aa	aa	82	259
+7	18	z	7	18	aa	aa	15	259
+7	18	z	7	18	aa	aa	20	259
+7	18	z	7	10	bb	bb	40	125
+7	18	z	7	10	bb	bb	32	125
+7	18	z	7	10	bb	bb	30	125
+7	18	z	7	10	bb	bb	23	125
+1	30	c	1	14	bb	bb	40	125
+1	30	c	1	14	bb	bb	32	125
+1	30	c	1	14	bb	bb	30	125
+1	30	c	1	14	bb	bb	23	125
+3	15	x	3	11	dd	dd	20	32
+3	15	x	3	11	dd	dd	12	32
+8	12	t	8	11	aa	aa	77	259
+8	12	t	8	11	aa	aa	50	259
+8	12	t	8	11	aa	aa	15	259
+8	12	t	8	11	aa	aa	82	259
+8	12	t	8	11	aa	aa	15	259
+8	12	t	8	11	aa	aa	20	259
+11	33	a	11	24	bbf	bbf	50	165
+11	33	a	11	24	bbf	bbf	42	165
+11	33	a	11	24	bbf	bbf	40	165
+11	33	a	11	24	bbf	bbf	33	165
+17	10	s	17	27	ccf	ccf	22	70
+17	10	s	17	27	ccf	ccf	28	70
+17	10	s	17	27	ccf	ccf	20	70
+17	10	s	17	28	aaf	aaf	87	319
+17	10	s	17	28	aaf	aaf	60	319
+17	10	s	17	28	aaf	aaf	25	319
+17	10	s	17	28	aaf	aaf	92	319
+17	10	s	17	28	aaf	aaf	25	319
+17	10	s	17	28	aaf	aaf	30	319
+17	10	s	17	20	bbf	bbf	50	165
+17	10	s	17	20	bbf	bbf	42	165
+17	10	s	17	20	bbf	bbf	40	165
+17	10	s	17	20	bbf	bbf	33	165
+11	20	v	11	24	bbf	bbf	50	165
+11	20	v	11	24	bbf	bbf	42	165
+11	20	v	11	24	bbf	bbf	40	165
+11	20	v	11	24	bbf	bbf	33	165
+12	23	y	12	22	aaf	aaf	87	319
+12	23	y	12	22	aaf	aaf	60	319
+12	23	y	12	22	aaf	aaf	25	319
+12	23	y	12	22	aaf	aaf	92	319
+12	23	y	12	22	aaf	aaf	25	319
+12	23	y	12	22	aaf	aaf	30	319
+12	23	y	12	21	aaf	aaf	87	319
+12	23	y	12	21	aaf	aaf	60	319
+12	23	y	12	21	aaf	aaf	25	319
+12	23	y	12	21	aaf	aaf	92	319
+12	23	y	12	21	aaf	aaf	25	319
+12	23	y	12	21	aaf	aaf	30	319
+17	18	a	17	27	ccf	ccf	22	70
+17	18	a	17	27	ccf	ccf	28	70
+17	18	a	17	27	ccf	ccf	20	70
+17	18	a	17	28	aaf	aaf	87	319
+17	18	a	17	28	aaf	aaf	60	319
+17	18	a	17	28	aaf	aaf	25	319
+17	18	a	17	28	aaf	aaf	92	319
+17	18	a	17	28	aaf	aaf	25	319
+17	18	a	17	28	aaf	aaf	30	319
+17	18	a	17	20	bbf	bbf	50	165
+17	18	a	17	20	bbf	bbf	42	165
+17	18	a	17	20	bbf	bbf	40	165
+17	18	a	17	20	bbf	bbf	33	165
+11	30	d	11	24	bbf	bbf	50	165
+11	30	d	11	24	bbf	bbf	42	165
+11	30	d	11	24	bbf	bbf	40	165
+11	30	d	11	24	bbf	bbf	33	165
+17	20	xf	17	27	ccf	ccf	22	70
+17	20	xf	17	27	ccf	ccf	28	70
+17	20	xf	17	27	ccf	ccf	20	70
+17	20	xf	17	28	aaf	aaf	87	319
+17	20	xf	17	28	aaf	aaf	60	319
+17	20	xf	17	28	aaf	aaf	25	319
+17	20	xf	17	28	aaf	aaf	92	319
+17	20	xf	17	28	aaf	aaf	25	319
+17	20	xf	17	28	aaf	aaf	30	319
+17	20	xf	17	20	bbf	bbf	50	165
+17	20	xf	17	20	bbf	bbf	42	165
+17	20	xf	17	20	bbf	bbf	40	165
+17	20	xf	17	20	bbf	bbf	33	165
+11	30	af	11	24	bbf	bbf	50	165
+11	30	af	11	24	bbf	bbf	42	165
+11	30	af	11	24	bbf	bbf	40	165
+11	30	af	11	24	bbf	bbf	33	165
+12	33	bf	12	22	aaf	aaf	87	319
+12	33	bf	12	22	aaf	aaf	60	319
+12	33	bf	12	22	aaf	aaf	25	319
+12	33	bf	12	22	aaf	aaf	92	319
+12	33	bf	12	22	aaf	aaf	25	319
+12	33	bf	12	22	aaf	aaf	30	319
+12	33	bf	12	21	aaf	aaf	87	319
+12	33	bf	12	21	aaf	aaf	60	319
+12	33	bf	12	21	aaf	aaf	25	319
+12	33	bf	12	21	aaf	aaf	92	319
+12	33	bf	12	21	aaf	aaf	25	319
+12	33	bf	12	21	aaf	aaf	30	319
+17	28	zf	17	27	ccf	ccf	22	70
+17	28	zf	17	27	ccf	ccf	28	70
+17	28	zf	17	27	ccf	ccf	20	70
+17	28	zf	17	28	aaf	aaf	87	319
+17	28	zf	17	28	aaf	aaf	60	319
+17	28	zf	17	28	aaf	aaf	25	319
+17	28	zf	17	28	aaf	aaf	92	319
+17	28	zf	17	28	aaf	aaf	25	319
+17	28	zf	17	28	aaf	aaf	30	319
+17	28	zf	17	20	bbf	bbf	50	165
+17	28	zf	17	20	bbf	bbf	42	165
+17	28	zf	17	20	bbf	bbf	40	165
+17	28	zf	17	20	bbf	bbf	33	165
+13	25	xf	13	21	ddf	ddf	30	52
+13	25	xf	13	21	ddf	ddf	22	52
+18	22	tf	18	21	aaf	aaf	87	319
+18	22	tf	18	21	aaf	aaf	60	319
+18	22	tf	18	21	aaf	aaf	25	319
+18	22	tf	18	21	aaf	aaf	92	319
+18	22	tf	18	21	aaf	aaf	25	319
+18	22	tf	18	21	aaf	aaf	30	319
+explain extended select *
+from t2, t3, (select c, b, sum(b) over (partition by c) from t4 ) t
+where t2.b < 40 and t2.a=t3.a and t3.c=t.c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	90	100.00	Using where
+1	PRIMARY	t3	ref	idx_a	idx_a	5	test.t2.a	2	100.00	Using where
+1	PRIMARY	<derived2>	ref	key0	key0	128	test.t3.c	10	100.00	
+2	DERIVED	t4	ALL	idx_c	NULL	NULL	NULL	160	100.00	Using temporary
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`t`.`c` AS `c`,`t`.`b` AS `b`,`t`.`sum(b) over (partition by c)` AS `sum(b) over (partition by c)` from `test`.`t2` join `test`.`t3` join (/* select#2 */ select `test`.`t4`.`c` AS `c`,`test`.`t4`.`b` AS `b`,sum(`test`.`t4`.`b`) over ( partition by `test`.`t4`.`c`) AS `sum(b) over (partition by c)` from `test`.`t4`) `t` where `test`.`t3`.`a` = `test`.`t2`.`a` and `t`.`c` = `test`.`t3`.`c` and `test`.`t2`.`b` < 40
+explain format=json select *
+from t2, t3, (select c, b, sum(b) over (partition by c) from t4 ) t
+where t2.b < 40 and t2.a=t3.a and t3.c=t.c;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t2",
+      "access_type": "ALL",
+      "rows": 90,
+      "filtered": 100,
+      "attached_condition": "t2.b < 40 and t2.a is not null"
+    },
+    "table": {
+      "table_name": "t3",
+      "access_type": "ref",
+      "possible_keys": ["idx_a"],
+      "key": "idx_a",
+      "key_length": "5",
+      "used_key_parts": ["a"],
+      "ref": ["test.t2.a"],
+      "rows": 2,
+      "filtered": 100,
+      "attached_condition": "t3.c is not null"
+    },
+    "table": {
+      "table_name": "<derived2>",
+      "access_type": "ref",
+      "possible_keys": ["key0"],
+      "key": "key0",
+      "key_length": "128",
+      "used_key_parts": ["c"],
+      "ref": ["test.t3.c"],
+      "rows": 10,
+      "filtered": 100,
+      "materialized": {
+        "query_block": {
+          "select_id": 2,
+          "window_functions_computation": {
+            "sorts": {
+              "filesort": {
+                "sort_key": "t4.c"
+              }
+            },
+            "temporary_table": {
+              "table": {
+                "table_name": "t4",
+                "access_type": "ALL",
+                "possible_keys": ["idx_c"],
+                "rows": 160,
                 "filtered": 100
               }
             }
@@ -13806,39 +14624,53 @@ CREATE TABLE t1 (i int);
 INSERT INTO t1 VALUES (1),(9),(3);
 CREATE TABLE t2 (a int, i int);
 INSERT INTO t2 VALUES (1,9),(2,3),(3,7),(4,1);
-CREATE TABLE t3 (a int, c varchar(8), index(c));
+CREATE TABLE t3 (a int, c char(127), index(c));
 INSERT INTO t3 VALUES (1,'foo'),(3,'bar'),(4,'foo'),(2,'bar');
-CREATE TABLE t4 (c varchar(8));
-INSERT INTO t4 VALUES ('abc'),('foo'),('def');
+INSERT INTO t3 SELECT a, concat(c,'a') FROM t3;
+CREATE TABLE t4 (a int, c char(127), index(a));
+INSERT INTO t4 VALUES
+(3,'abc'),(1,'foo'),(4,'def'),(8,'xxx'),(3,'yyy'),
+(5,'zzz'),(9,'xyz'),(2,'yxz'),(5,'zxy'),(7,'zyx') ;
+ANALYZE TABLE t1,t2,t3,t4;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+test.t2	analyze	status	OK
+test.t3	analyze	status	OK
+test.t4	analyze	status	OK
 CREATE VIEW v1 AS
 SELECT c FROM t3
 WHERE a IN ( SELECT t2.a FROM t1 JOIN t2 WHERE t1.i = t2.i ) GROUP BY c ;
-set statement optimizer_switch='split_grouping_derived=off' for SELECT * FROM t4 WHERE c IN ( SELECT c FROM v1 );
-c
-foo
-SELECT * FROM t4 WHERE c IN ( SELECT c FROM v1 );
-c
-foo
-explain extended SELECT * FROM t4 WHERE c IN ( SELECT c FROM v1 );
+set statement optimizer_switch='split_materialized=off' for SELECT * FROM t4 WHERE c IN ( SELECT c FROM v1 ) and a < 2;
+a	c
+1	foo
+SELECT * FROM t4 WHERE c IN ( SELECT c FROM v1 ) and a < 2;
+a	c
+1	foo
+explain extended SELECT * FROM t4 WHERE c IN ( SELECT c FROM v1 ) and a < 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
-1	PRIMARY	<derived3>	ref	key0	key0	11	test.t4.c	4	100.00	FirstMatch(t4)
-3	LATERAL DERIVED	t3	ALL	c	NULL	NULL	NULL	4	75.00	Using where
+1	PRIMARY	t4	range	a	a	5	NULL	1	100.00	Using index condition; Using where
+1	PRIMARY	<derived3>	ref	key0	key0	128	test.t4.c	2	100.00	FirstMatch(t4)
+3	LATERAL DERIVED	t3	ref	c	c	128	test.t4.c	2	100.00	
 3	LATERAL DERIVED	<subquery4>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
 4	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
 4	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where; Using join buffer (flat, BNL join)
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t4`.`c` AS `c` from `test`.`t4` semi join (`test`.`v1`) where `v1`.`c` = `test`.`t4`.`c`
-explain format=json SELECT * FROM t4 WHERE c IN ( SELECT c FROM v1 );
+Note	1003	/* select#1 */ select `test`.`t4`.`a` AS `a`,`test`.`t4`.`c` AS `c` from `test`.`t4` semi join (`test`.`v1`) where `v1`.`c` = `test`.`t4`.`c` and `test`.`t4`.`a` < 2
+explain format=json SELECT * FROM t4 WHERE c IN ( SELECT c FROM v1 ) and a < 2;
 EXPLAIN
 {
   "query_block": {
     "select_id": 1,
     "table": {
       "table_name": "t4",
-      "access_type": "ALL",
-      "rows": 3,
+      "access_type": "range",
+      "possible_keys": ["a"],
+      "key": "a",
+      "key_length": "5",
+      "used_key_parts": ["a"],
+      "rows": 1,
       "filtered": 100,
+      "index_condition": "t4.a < 2",
       "attached_condition": "t4.c is not null"
     },
     "table": {
@@ -13846,23 +14678,27 @@ EXPLAIN
       "access_type": "ref",
       "possible_keys": ["key0"],
       "key": "key0",
-      "key_length": "11",
+      "key_length": "128",
       "used_key_parts": ["c"],
       "ref": ["test.t4.c"],
-      "rows": 4,
+      "rows": 2,
       "filtered": 100,
       "first_match": "t4",
       "materialized": {
         "query_block": {
           "select_id": 3,
           "const_condition": "1",
+          "outer_ref_condition": "t4.c is not null",
           "table": {
             "table_name": "t3",
-            "access_type": "ALL",
+            "access_type": "ref",
             "possible_keys": ["c"],
-            "rows": 4,
-            "filtered": 75,
-            "attached_condition": "t3.c = t4.c"
+            "key": "c",
+            "key_length": "128",
+            "used_key_parts": ["c"],
+            "ref": ["test.t4.c"],
+            "rows": 2,
+            "filtered": 100
           },
           "table": {
             "table_name": "<subquery4>",
diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result
index cb538a352bc..0e9a0d3dc5d 100644
--- a/mysql-test/r/mysqld--help.result
+++ b/mysql-test/r/mysqld--help.result
@@ -665,7 +665,7 @@ The following options may be given as the first argument:
  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_grouping_derived
+ condition_pushdown_for_derived, split_materialized
  --optimizer-use-condition-selectivity=# 
  Controls selectivity of which conditions the optimizer
  takes into account to calculate cardinality of a partial
@@ -1510,7 +1510,7 @@ old-style-user-limits FALSE
 optimizer-prune-level 1
 optimizer-search-depth 62
 optimizer-selectivity-sampling-limit 100
-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_grouping_derived=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
 optimizer-use-condition-selectivity 1
 performance-schema FALSE
 performance-schema-accounts-size -1
diff --git a/mysql-test/r/selectivity.result b/mysql-test/r/selectivity.result
index cdb210ae2d9..9cb6ee3e9bf 100644
--- a/mysql-test/r/selectivity.result
+++ b/mysql-test/r/selectivity.result
@@ -141,7 +141,7 @@ order by s_suppkey;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	supplier	ALL	PRIMARY	NULL	NULL	NULL	10	100.00	Using filesort
 1	PRIMARY	<derived3>	ref	key0	key0	5	dbt3_s001.supplier.s_suppkey	10	100.00	Using where
-3	LATERAL DERIVED	lineitem	range	i_l_shipdate,i_l_suppkey	i_l_shipdate	4	NULL	268	75.00	Using where
+3	DERIVED	lineitem	range	i_l_shipdate,i_l_suppkey	i_l_shipdate	4	NULL	268	100.00	Using where; Using temporary; Using filesort
 2	SUBQUERY	<derived4>	ALL	NULL	NULL	NULL	NULL	268	100.00	
 4	DERIVED	lineitem	range	i_l_shipdate	i_l_shipdate	4	NULL	268	100.00	Using where; Using temporary; Using filesort
 Warnings:
@@ -162,7 +162,7 @@ order by s_suppkey;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	supplier	ALL	PRIMARY	NULL	NULL	NULL	10	100.00	Using filesort
 1	PRIMARY	<derived3>	ref	key0	key0	5	dbt3_s001.supplier.s_suppkey	10	100.00	Using where
-3	LATERAL DERIVED	lineitem	range	i_l_shipdate,i_l_suppkey	i_l_shipdate	4	NULL	268	100.00	Using where
+3	DERIVED	lineitem	range	i_l_shipdate,i_l_suppkey	i_l_shipdate	4	NULL	268	100.00	Using where; Using temporary; Using filesort
 2	SUBQUERY	<derived4>	ALL	NULL	NULL	NULL	NULL	268	100.00	
 4	DERIVED	lineitem	range	i_l_shipdate	i_l_shipdate	4	NULL	268	100.00	Using where; Using temporary; Using filesort
 Warnings:
diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result
index 47d2e6fd554..236647c6091 100644
--- a/mysql-test/r/selectivity_innodb.result
+++ b/mysql-test/r/selectivity_innodb.result
@@ -144,7 +144,7 @@ order by s_suppkey;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	supplier	index	PRIMARY	PRIMARY	4	NULL	10	100.00	
 1	PRIMARY	<derived3>	ref	key0	key0	5	dbt3_s001.supplier.s_suppkey	10	100.00	Using where
-3	LATERAL DERIVED	lineitem	range	i_l_shipdate,i_l_suppkey	i_l_shipdate	4	NULL	229	75.11	Using where
+3	DERIVED	lineitem	range	i_l_shipdate,i_l_suppkey	i_l_shipdate	4	NULL	229	100.00	Using where; Using temporary; Using filesort
 2	SUBQUERY	<derived4>	ALL	NULL	NULL	NULL	NULL	229	100.00	
 4	DERIVED	lineitem	range	i_l_shipdate	i_l_shipdate	4	NULL	229	100.00	Using where; Using temporary; Using filesort
 Warnings:
@@ -165,7 +165,7 @@ order by s_suppkey;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	supplier	index	PRIMARY	PRIMARY	4	NULL	10	100.00	
 1	PRIMARY	<derived3>	ref	key0	key0	5	dbt3_s001.supplier.s_suppkey	10	100.00	Using where
-3	LATERAL DERIVED	lineitem	range	i_l_shipdate,i_l_suppkey	i_l_shipdate	4	NULL	229	100.00	Using where
+3	DERIVED	lineitem	range	i_l_shipdate,i_l_suppkey	i_l_shipdate	4	NULL	229	100.00	Using where; Using temporary; Using filesort
 2	SUBQUERY	<derived4>	ALL	NULL	NULL	NULL	NULL	228	100.00	
 4	DERIVED	lineitem	range	i_l_shipdate	i_l_shipdate	4	NULL	229	100.00	Using where; Using temporary; Using filesort
 Warnings:
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 cb3291dadea..87c837986ac 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_grouping_derived=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
 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_grouping_derived=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
 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_grouping_derived=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
 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_grouping_derived=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
 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_grouping_derived=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
 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_grouping_derived=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
 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_grouping_derived=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
 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_grouping_derived=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
 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_grouping_derived=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
 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_grouping_derived=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
 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_grouping_derived=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
 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_grouping_derived=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
 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_grouping_derived=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
 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_grouping_derived=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
 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_grouping_derived=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
 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_grouping_derived=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
 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_grouping_derived=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
 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_grouping_derived=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
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 3ba61fa8915..d087df07278 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result
@@ -8,6 +8,7 @@ where variable_name not like 'aria%' and
 variable_name not like 'debug%' and
 variable_name not like 'wsrep%' and
 variable_name not in (
+'in_predicate_conversion_threshold',
 'have_openssl',
 'have_symlink',
 'hostname',
@@ -2672,17 +2673,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_grouping_derived=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_grouping_derived=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
+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
 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_grouping_derived=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
 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_grouping_derived,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,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 4f5a08211f5..8b1ce78f960 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
@@ -2883,17 +2883,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_grouping_derived=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_grouping_derived=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
+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
 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_grouping_derived=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
 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_grouping_derived,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,default
 READ_ONLY	NO
 COMMAND_LINE_ARGUMENT	REQUIRED
 VARIABLE_NAME	OPTIMIZER_USE_CONDITION_SELECTIVITY
diff --git a/mysql-test/t/derived_cond_pushdown.test b/mysql-test/t/derived_cond_pushdown.test
index 2a3da09a3d7..787c3032f53 100644
--- a/mysql-test/t/derived_cond_pushdown.test
+++ b/mysql-test/t/derived_cond_pushdown.test
@@ -2145,7 +2145,7 @@ drop table t1;
 --echo #
 
 set @save_optimizer_switch= @@optimizer_switch;
-set optimizer_switch='split_grouping_derived=off';
+set optimizer_switch='split_materialized=off';
              
 create table t1 (a int, c varchar(16));
 insert into t1 values
@@ -2256,28 +2256,62 @@ set optimizer_switch= @save_optimizer_switch;
 --echo #
 
 let
-$no_splitting= set statement optimizer_switch='split_grouping_derived=off' for; 
+$no_splitting= set statement optimizer_switch='split_materialized=off' for; 
 
-create table t1 (a int);
+create table t1 (a int, b int, index idx_b(b)) engine=myisam;
 insert into t1 values
-(8), (5), (1), (2), (9), (7), (2), (7);
+(8,3), (5,7), (1,2), (2,1), (9,7), (7,5), (2,2), (7,3),
+(9,3), (8,1), (4,5), (2,3);
 
-create table t2 (a int, b int, index idx(a));
+create table t2 (a int, b int, c char(127), index idx_a(a)) engine=myisam;
 insert into t2 values
-  (7,10), (1,20), (2,23), (7,18), (1,30),
-  (4,71), (3,15), (7,82), (8,12), (4,15),
-  (11,33), (10,42), (4,53), (10,17), (2,90);
+  (7,10,'x'), (1,20,'a'), (2,23,'b'), (7,18,'z'), (1,30,'c'),
+  (4,71,'d'), (3,15,'x'), (7,82,'y'), (8,12,'t'), (4,15,'b'),
+  (11,33,'a'), (10,42,'u'), (4,53,'p'), (10,17,'r'), (2,90,'x'),
+  (17,10,'s'), (11,20,'v'), (12,23,'y'), (17,18,'a'), (11,30,'d'),
+  (24,71,'h'), (23,15,'i'), (27,82,'k'), (28,12,'p'), (24,15,'q'),
+  (31,33,'f'), (30,42,'h'), (40,53,'m'), (30,17,'o'), (21,90,'b'),
+  (37,10,'e'), (31,20,'g'), (32,23,'f'), (37,18,'n'), (41,30,'l'),
+  (54,71,'j'), (53,15,'w'), (57,82,'z'), (58,12,'k'), (54,15,'p'),
+  (61,33,'c'), (60,42,'a'), (62,53,'x'), (67,17,'g'), (64,90,'v');
+
+insert into t2 select a+10, b+10, concat(c,'f') from t2;
+
+analyze table t1,t2;
 
 let $q1=
-select t1.a,t.max,t.min
+select t1.a,t.s,t.m
 from t1 join
-     (select a, max(t2.b) max, min(t2.b) min from t2 group by t2.a) t
-     on t1.a=t.a;
+     (select a, sum(t2.b) as s, min(t2.c) as m from t2 group by t2.a) t
+     on t1.a=t.a
+where t1.b < 3;
 
 eval $no_splitting $q1;
 eval $q1;
 eval explain extended $q1;
-eval explain format=json $q1;  
+eval explain format=json $q1;
+eval prepare stmt from "$q1";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+let $q10=
+select t1.a,t.s,t.m
+from t1 join
+     (select a, sum(t2.b) as s, min(t2.b) as m from t2 group by t2.a) t
+     on t1.a=t.a
+where t1.b <= 5;
+
+eval $no_splitting $q10;
+eval $q10;
+eval explain extended $q10;
+eval explain format=json $q10;
+eval prepare stmt from "$q10";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+delete from t1 where t1.b between 2 and 5;
 
 let $q2=
 select t1.a,t.max,t.min
@@ -2290,68 +2324,122 @@ eval $q2;
 eval explain extended $q2;
 eval explain format=json $q2;
 
-create table t3 (a int, c varchar(16));
+create table t3 (a int, b int, c char(127), index idx_b(b)) engine=myisam;
 insert into t3 values
-(8,'aa'), (5,'cc'), (1,'bb'), (2,'aa'), (9,'cc'),
-(7,'aa'), (2,'aa'), (7,'bb');
+(8,11,'aa'), (5,15,'cc'), (1,14,'bb'), (2,12,'aa'), (7,17,'cc'),
+(7,18,'aa'), (2,11,'aa'), (7,10,'bb'), (3,11,'dd'), (4,12,'ee'),
+(5,14,'dd'), (9,12,'ee');
   
-create table t4 (a int, b int, c varchar(16), index idx(a,c));
+create table t4 (a int, b int, c char(127), index idx(a,c)) engine=myisam;
 insert into t4 values
   (7,10,'cc'), (1,20,'aa'), (2,23,'bb'), (7,18,'cc'), (1,30,'bb'),
-  (4,71,'xx'), (3,15,'aa'), (7,82,'bb'), (8,12,'dd'), (4,15,'aa'),
-  (11,33,'yy'), (10,42,'zz'), (4,53,'xx'), (10,17,'yy'), (7,12,'bb'),
+  (4,71,'xx'), (3,15,'aa'), (7,82,'aa'), (8,12,'dd'), (4,15,'aa'),
+  (11,33,'yy'), (10,42,'zz'), (4,53,'xx'), (10,17,'yy'), (7,12,'cc'),
   (8,20,'dd'), (7,32,'bb'), (1,50,'aa'), (3,40,'bb'), (3,77,'aa');
 
+insert into t4 select a+10, b+10, concat(c,'f') from t4;
+
+analyze table t3,t4;
+
 let $q3=
 select t3.a,t3.c,t.max,t.min
 from t3 join
      (select a, c, max(b) max, min(b) min from t4 group by a,c) t
-     on t3.a=t.a and t3.c=t.c;
+     on t3.a=t.a and t3.c=t.c
+where t3.b > 15;
 
 eval $no_splitting $q3;
 eval $q3;
 eval explain extended $q3;
 eval explain format=json $q3;  
 
+let $q30=
+select t3.a,t3.c,t.max,t.min
+from t3 join
+     (select a, c, max(b) max, min(b) min from t4 group by a,c) t
+     on t3.a=t.a and t3.c=t.c
+where t3.b <= 15;
+
+eval $no_splitting $q30;
+eval $q30;
+eval explain extended $q30;
+eval explain format=json $q30;  
+
 let $q4=
 select t3.a,t3.c,t.max,t.min
 from t3 join
      (select a, c, max(b) max, min(b) min from t4 group by c,a) t
-     on t3.a=t.a and t3.c=t.c;
+     on t3.a=t.a and t3.c=t.c
+where t3.b > 15;
 
 eval $no_splitting $q4;
 eval $q4;
 eval explain extended $q4;
 eval explain format=json $q4;  
 
-drop index idx on t2;
-create index idx on t2(b);
-create index idx on t3(a);
-create index idx2 on t4(c);
-insert into t3 select a+1, concat(c,'f') from t3;
-insert into t3 select a+1, concat(c,'h')  from t3;
-insert into t4 select a+1, b+10, concat(c,'h') from t4;
+let $q40=
+select t3.a,t3.c,t.max,t.min
+from t3 join
+     (select a, c, max(b) max, min(b) min from t4 group by c,a) t
+     on t3.a=t.a and t3.c=t.c
+where t3.b <= 15;
+
+eval $no_splitting $q40;
+eval $q40;
+eval explain extended $q40;
+eval explain format=json $q40;  
+
+drop index idx_a on t2;
+create index idx on t2(c,b);
+create index idx_a on t3(a);
+create index idx_c on t4(c);
+insert into t3 select a+10, b+10, concat(c,'f') from t3;
+insert into t3 select a+100, b+100, concat(c,'g')  from t3;
+insert into t4 select a+100, b+100, concat(c,'g') from t4;
+insert into t4 select a+1000, b+1000, concat(c,'h') from t4;
+
+analyze table t2,t3,t4;
 
 let $q5=
-select t2.a,t2.b,t3.c,t.max,t.min
+select t2.a,t2.b,t2.c,t.c as t_c,t.max,t.min
 from t2, t3, (select c, max(b) max, min(b) min from t4 group by c) t
-where t2.b > 50 and t2.a=t3.a and t3.c=t.c;
+where t2.b between 80 and 85 and t2.c in ('y','z') and t2.a=t3.a and t3.c=t.c;
 
 eval $no_splitting $q5;
 eval $q5;
 eval explain extended $q5;
 eval explain format=json $q5;
 
+let $q50=
+select t2.a,t2.b,t2.c,t.c as t_c,t.max,t.min
+from t2, t3, (select c, max(b) max, min(b) min from t4 group by c) t
+where t2.b < 40 and t2.a=t3.a and t3.c=t.c;
+
+eval $no_splitting $q50;
+eval $q50;
+eval explain extended $q50;
+eval explain format=json $q50;
+
 let $q6=
 select *
 from t2, t3, (select c, b, sum(b) over (partition by c) from t4 ) t
-where t2.b > 50 and t2.a=t3.a and t3.c=t.c;
+where t2.b between 80 and 85 and t2.c in ('y','z') and t2.a=t3.a and t3.c=t.c;
 
 eval $no_splitting $q6;
 eval $q6;
 eval explain extended $q6;
 eval explain format=json $q6;
 
+let $q60=
+select *
+from t2, t3, (select c, b, sum(b) over (partition by c) from t4 ) t
+where t2.b < 40 and t2.a=t3.a and t3.c=t.c;
+
+eval $no_splitting $q60;
+eval $q60;
+eval explain extended $q60;
+eval explain format=json $q60;
+
 drop table t1,t2,t3,t4;
 
 --echo #
@@ -2365,18 +2453,23 @@ INSERT INTO t1 VALUES (1),(9),(3);
 CREATE TABLE t2 (a int, i int);
 INSERT INTO t2 VALUES (1,9),(2,3),(3,7),(4,1);
 
-CREATE TABLE t3 (a int, c varchar(8), index(c));
+CREATE TABLE t3 (a int, c char(127), index(c));
 INSERT INTO t3 VALUES (1,'foo'),(3,'bar'),(4,'foo'),(2,'bar');
+INSERT INTO t3 SELECT a, concat(c,'a') FROM t3;
+
+CREATE TABLE t4 (a int, c char(127), index(a));
+INSERT INTO t4 VALUES
+  (3,'abc'),(1,'foo'),(4,'def'),(8,'xxx'),(3,'yyy'),
+  (5,'zzz'),(9,'xyz'),(2,'yxz'),(5,'zxy'),(7,'zyx') ;
 
-CREATE TABLE t4 (c varchar(8));
-INSERT INTO t4 VALUES ('abc'),('foo'),('def');
+ANALYZE TABLE t1,t2,t3,t4;
 
 CREATE VIEW v1 AS
 SELECT c FROM t3
   WHERE a IN ( SELECT t2.a FROM t1 JOIN t2 WHERE t1.i = t2.i ) GROUP BY c ;
 
 let $q1=
-SELECT * FROM t4 WHERE c IN ( SELECT c FROM v1 );
+SELECT * FROM t4 WHERE c IN ( SELECT c FROM v1 ) and a < 2;
 
 eval $no_splitting $q1;
 eval $q1;
diff --git a/sql/CMakeLists.txt b/sql/CMakeLists.txt
index 24e1dd27d02..9f796896a41 100644
--- a/sql/CMakeLists.txt
+++ b/sql/CMakeLists.txt
@@ -145,6 +145,7 @@ SET (SQL_SOURCE
 	       sql_cte.cc
                sql_sequence.cc sql_sequence.h ha_sequence.h
                sql_tvc.cc sql_tvc.h
+               opt_split.cc
 	       ${WSREP_SOURCES}
                table_cache.cc encryption.cc temporary_tables.cc
                proxy_protocol.cc
diff --git a/sql/opt_split.cc b/sql/opt_split.cc
new file mode 100644
index 00000000000..786ed7a27c3
--- /dev/null
+++ b/sql/opt_split.cc
@@ -0,0 +1,1132 @@
+/*
+   Copyright (c) 2017 MariaDB
+
+   This program is free software; you can redistribute it and/or modify
+   it under the terms of the GNU General Public License as published by
+   the Free Software Foundation; version 2 of the License.
+
+   This program is distributed in the hope that it will be useful,
+   but WITHOUT ANY WARRANTY; without even the implied warranty of
+   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+   GNU General Public License for more details.
+
+   You should have received a copy of the GNU General Public License
+   along with this program; if not, write to the Free Software
+   Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301  USA */
+
+/*
+  This file contains functions to support the splitting technique.
+  This optimization technique can be applied to equi-joins involving
+  materialized tables such as materialized views, materialized derived tables
+  and materialized CTEs. The technique also could be applied to materialized
+  semi-joins though the code below does not support this usage yet.
+
+  Here are the main ideas behind this technique that we'll call SM optimization
+  (SplitMaterialization).
+
+  Consider the query
+    SELECT t1.a, t.min
+      FROM t1, (SELECT t2.a, MIN(t2.b) as min FROM t2 GROUP BY t2.a) t
+    WHERE t1.a = t.a and t1.b < const
+
+  Re-write the query into
+    SELECT t1.a, t.min
+      FROM t1, LATERAL (SELECT t2.a, MIN(t2.b) as min
+                        FROM t2 WHERE t2.a = t1.a GROUP BY t2.a) t
+    WHERE t1.b < const
+
+  The execution of the original query (Q1) does the following:
+    1. Executes the query in the specification of the derived table
+       and puts the result set into a temporary table with an index
+       on the first column.
+    2. Joins t1 with the temporary table using the its index.
+
+  The execution of the transformed query (Q1R) follows these steps:
+    1. For each row of t1 where t1.b < const a temporary table
+       containing all rows of of t2 with t2.a = t1.a is created
+    2. If there are any rows in the temporary table aggregation
+       is performed for them
+    3. The result of the aggregation is joined with t1.
+
+  The second execution can win if:
+    a) There is an efficient way to select rows of t2 for which t2.a = t1.a
+       (For example if there is an index on t2.a)
+    and
+    b) The number of temporary tables created for partitions
+       is much smaller that the total number of partitions
+
+  It should be noted that for the transformed query aggregation
+  for a partition may be performed several times.
+
+  As we can see the optimization basically splits table t2 into
+  partitions and performs aggregation over each of them
+  independently.
+
+  If we have only one equi-join condition then we either push it as
+  for Q1R or we don't. In a general case we may have much more options.
+  Consider the query (Q3)
+    SELECT
+      FROM t1,t2 (SELECT t3.a, t3.b, MIN(t3.c) as min
+                  FROM t3 GROUP BY a,b) t
+    WHERE t.a = t1.a AND t.b = t2.b
+          AND t1.c < c1 and t2.c < c2
+          AND P(t1,t2);
+  (P(t1,t2) designates  some additional conditions over columns of t1,t2).
+
+  Assuming that there indexes on t3(a,b) and t3(b) here we have several
+  reasonable options to push equi-join conditions into the derived.
+  All these options should be taken into account when the optimizer
+  evaluates different join orders. When the join order (t1,t,t2) is
+  evaluated there is only one way of splitting : to push the condition
+  t.a = t1.a into t. With the join order (t2,t,t1) only the condition
+  t.b = t2.b can be pushed. When the join orders (t1,t2,t) and (t2,t1,t)
+  are evaluated then the optimizer should consider pushing t.a = t1.a,
+  t.b = t2.b and (t.a = t1.a AND t.b = t2.b) to choose the best condition
+  for splitting. Apparently here last condition is the best one because
+  it provides the miximum possible number of partitions.
+
+  If we dropped the index on t3(a,b) and created the index on t3(a) instead
+  then we would have two options for splitting: to push t.a = t1.a or to
+  push t.b = t2.b. If the selectivity of the index t3(a) is better than
+  the selectivity of t3(b) then the first option is preferred.
+
+  Although the condition (t.a = t1.a AND t.b = t2.b) provides a better
+  splitting than the condition t.a = t1.a the latter will be used for
+  splitting if the execution plan with the join order (t1,t,t2) turns out
+  to be the cheapest one. It's quite possible when the join condition
+  P(t1,t2) has a bad selectivity.
+
+  Whenever the optimizer evaluates the cost of using a splitting it
+  compares it with the cost of materialization without splitting.
+
+  If we just drop the index on t3(a,b) the chances that the splitting
+  will be used becomes much lower but they still exists providing that
+  the fanout of the partial join of t1 and t2 is small enough.
+*/
+
+/*
+  Splitting can be applied to a materialized table specified by the query
+  with post-join operations that require partitioning of the result set produced
+  by the join expression used in the FROM clause the query such as GROUP BY
+  operation and window function operation. In any of these cases the post-join
+  operation can be executed independently for any partition only over the rows
+  of this partition. Also if the set of all partitions is divided into disjoint
+  subsets the operation can applied to each subset independently. In this case
+  all rows are first partitioned into the groups each of which contains all the
+  rows from the partitions belonging the same subset and then each group
+  is subpartitioned into groups in the the post join operation.
+
+  The set of all rows belonging to the union of several partitions is called
+  here superpartition. If a grouping operation is defined by the list
+  e_1,...,e_n then any set S = {e_i1,...,e_ik} can be used to devide all rows
+  into superpartions such that for any two rows r1, r2  the following holds:
+  e_ij(r1) = e_ij(r2) for each e_ij from S. We use the splitting technique
+  only if S consists of references to colums  of the joined tables.
+  For example if the GROUP BY list looks like this a, g(b), c we can consider
+  applying the splitting technique to the superpartitions defined by {a,c},
+  {a}, {c} (a and c here may be the references to the columns from different
+  tables).
+*/
+
+ /*
+   The following describes when and how the optimizer decides whether it
+   makes sense to employ the splitting technique.
+
+   1. For each instance of a materialized table (derived/view/CTE) it is
+      checked that it is potentially splittable. Now it is done right after the
+      execution plan for the select specifying this table has been chosen.
+
+   2. Any potentially splittable materialized table T is subject to two-phase
+      optimization. It means that the optimizer first builds the best execution
+      plan for join that specifies T. Then the control is passed back to the
+      optimization process of the embedding select Q. After the execution plan
+      for Q has been chosen the optimizer finishes the optimization of the join
+      specifying T.
+
+   3. When the optimizer builds the container with the KEYUSE structures
+      for the join of embedding select it detects the equi-join conditions
+      PC that potentially could be pushed into a potentially splittable
+      materialized table T. The collected information about such conditions
+      is stored together with other facts on potential splittings for table T.
+
+   4. When the optimizer starts looking for the best execution plan for the
+      embedding select Q for each potentially splittable materialized table T
+      it creates special KEYUSE structures for pushable equi-join conditions
+      PC. These structures are used to add new elements to the container
+      of KEYUSE structures built for T. The specifics of these elements is
+      that they can be ebabled and disabled during the process of choosing
+      the best plan for Q.
+
+   5. When the optimizer extends a partial join order with a potentially
+      splittable materialized table T (in function best_access_path) it
+      first evaluates a new execution plan for the modified specification
+      of T that adds all equi-join conditions that can be pushed with
+      current join prefix to the WHERE conditions of the original
+      specification of T. If the cost of the new plan is better than the
+      the cost of the original materialized table then the optimizer
+      prefers to use splitting for the current join prefix. As the cost
+      of the plan depends only on the pushed conditions it makes sense
+      to cache this plan for other prefixes.
+
+   6. The optimizer takes into account the cost of splitting / materialization
+      of a potentially splittable materialized table T as a startup cost
+      to access table T.
+
+   7. When the optimizer finally chooses the best execution plan for
+      the embedding select Q and this plan prefers using splitting
+      for table T with pushed equi-join conditions PC then the execution
+      plan for the underlying join with these conditions is chosen for T.
+*/
+
+/*
+  The implementation of the splitting technique below allows to apply
+  the technique only to a materialized derived table / view / CTE whose
+  specification is either a select with GROUP BY or a non-grouping select
+  with window functions that share the same PARTITION BY list.
+*/
+
+#include "mariadb.h"
+#include "sql_select.h"
+
+/* Info on a splitting field */
+struct SplM_field_info
+{
+  /* Splitting field in the materialized table T */
+  Field *mat_field;
+  /* The item from the select list of the specification of T */
+  Item *producing_item;
+  /* The corresponding splitting field from the specification of T */
+  Field *underlying_field;
+};
+
+
+/* Info on the splitting execution plan saved in SplM_opt_info::cache */
+struct SplM_plan_info
+{
+  /* The cached splitting execution plan P */
+  struct st_position *best_positions;
+  /* The cost of the above plan */
+  double cost;
+  /* Selectivity of splitting used in P */
+  double split_sel;
+  /* For fast search of KEYUSE_EXT elements used for splitting in P */
+  struct KEYUSE_EXT *keyuse_ext_start;
+  /* The tables that contains the fields used for splitting in P */
+  TABLE *table;
+  /* The number of the key from 'table' used for splitting in P */
+  uint key;
+  /* Number of the components of 'key' used for splitting in P */
+  uint parts;
+};
+
+
+/*
+  The structure contains the information that is used by the optimizer
+  for potentially splittable materialization of T  that is a materialized
+  derived_table / view / CTE
+*/
+class SplM_opt_info : public Sql_alloc
+{
+public:
+  /* The join for the select specifying T */
+  JOIN *join;
+  /* The map of tables from 'join' whose columns can be used for partitioning */  
+  table_map tables_usable_for_splitting;
+  /* Info about the fields of the joined tables usable for splitting */
+  SplM_field_info *spl_fields;
+  /* The number of elements in the above list */
+  uint spl_field_cnt;
+  /* Contains the structures to generate all KEYUSEs for pushable equalities */
+  List<KEY_FIELD> added_key_fields;
+  /* The cache of evaluated execution plans for 'join' with pushed equalities */
+  List<SplM_plan_info> plan_cache;
+  /* Cost of best execution plan for join when nothing is pushed */
+  double unsplit_cost;
+  /* Cardinality of T when nothing is pushed */
+  double unsplit_card;
+  /* Lastly evaluated execution plan for 'join' with pushed equalities */
+  SplM_plan_info *last_plan;
+
+  SplM_plan_info *find_plan(TABLE *table, uint key, uint parts);
+};
+
+
+void TABLE::set_spl_opt_info(SplM_opt_info *spl_info)
+{
+  if (spl_info)
+    spl_info->join->spl_opt_info= spl_info;
+  spl_opt_info= spl_info;
+}
+
+
+void TABLE::deny_splitting()
+{
+  DBUG_ASSERT(spl_opt_info != NULL);
+  spl_opt_info->join->spl_opt_info= NULL;
+  spl_opt_info= NULL;
+}
+
+
+/* This structure is auxiliary and used only in the function that follows it */
+struct SplM_field_ext_info: public SplM_field_info
+{
+  uint item_no;
+  bool is_usable_for_ref_access;
+};
+
+
+/**
+  @brief
+    Check whether this join is one for potentially splittable materialized table
+
+  @details
+    The function checks whether this join is for select that specifies
+    a potentially splittable materialized table T. If so, the collected
+    info on potential splittability of T is attached to the field spl_opt_info
+    of the TABLE structure for T.
+
+    The function returns a positive answer if the following holds:
+    1. the optimizer switch 'split_materialized' is set 'on'
+    2. the select owning this join specifies a materialized derived/view/cte T
+    3. this is the only select in the specification of T
+    4. condition pushdown is not prohibited into T
+    5. T is not recursive
+    6. T is either
+       6.1. a grouping table with GROUP BY list P
+       or
+       6.2. a non-grouping table with window functions over the same non-empty
+            partition specified by the PARTITION BY list P
+    7. P contains some references on the columns of the joined tables C
+       occurred also in the select list of this join
+    8. There are defined some keys usable for ref access of fields from C
+       with available statistics. 
+
+  @retval
+    true   if the answer is positive
+    false  otherwise
+*/
+
+bool JOIN::check_for_splittable_materialized()
+{
+  ORDER *partition_list= 0;
+  st_select_lex_unit *unit= select_lex->master_unit();
+  TABLE_LIST *derived= unit->derived;
+  if (!(optimizer_flag(thd, OPTIMIZER_SWITCH_SPLIT_MATERIALIZED)) ||  // !(1)
+      !(derived && derived->is_materialized_derived()) ||             // !(2)
+      (unit->first_select()->next_select()) ||                        // !(3)
+      (derived->prohibit_cond_pushdown) ||                            // !(4)
+      (derived->is_recursive_with_table()))                           // !(5)
+    return false;
+  if (group_list)                                                     // (6.1)
+  {
+    if (!select_lex->have_window_funcs())
+      partition_list= group_list;
+  }
+  else if (select_lex->have_window_funcs() &&
+           select_lex->window_specs.elements == 1)                    // (6.2)
+  {
+    partition_list=
+      select_lex->window_specs.head()->partition_list->first;
+  }
+  if (!partition_list)
+    return false;
+
+  ORDER *ord;
+  Dynamic_array<SplM_field_ext_info> candidates;
+
+  /*
+    Select from partition_list all candidates for splitting.
+    A candidate must be
+    - field item or refer to such (7.1)
+    - item mentioned in the select list (7.2)
+    Put info about such candidates into the array candidates
+  */
+  table_map usable_tables= 0;  // tables that contains the candidate
+  for (ord= partition_list; ord; ord= ord->next)
+  {
+    Item *ord_item= *ord->item;
+    if (ord_item->real_item()->type() != Item::FIELD_ITEM)   // !(7.1)
+      continue;
+
+    Field *ord_field= ((Item_field *) (ord_item->real_item()))->field;
+
+    JOIN_TAB *tab= ord_field->table->reginfo.join_tab;
+    if (tab->is_inner_table_of_outer_join())
+      continue;
+
+    List_iterator<Item> li(fields_list);
+    Item *item;
+    uint item_no= 0;
+    while ((item= li++))
+    {
+      if ((*ord->item)->eq(item, 0))       // (7.2)
+      {
+	SplM_field_ext_info new_elem;
+        new_elem.producing_item= item;
+        new_elem.item_no= item_no;
+        new_elem.mat_field= derived->table->field[item_no];
+        new_elem.underlying_field= ord_field;
+        new_elem.is_usable_for_ref_access= false;
+        candidates.push(new_elem);
+        usable_tables|= ord_field->table->map;
+        break;
+      }
+      item_no++;
+    }
+  }
+  if (candidates.elements() == 0)  // no candidates satisfying (7.1) && (7.2)
+    return false;
+
+  /*
+    For each table from this join find the keys that can be used for ref access
+    of the fields mentioned in the 'array candidates'
+  */
+
+  SplM_field_ext_info *cand;
+  SplM_field_ext_info *cand_start= &candidates.at(0);
+  SplM_field_ext_info *cand_end= cand_start + candidates.elements();
+
+  for (JOIN_TAB *tab= join_tab;
+       tab < join_tab + top_join_tab_count; tab++)
+  {
+    TABLE *table= tab->table;
+    if (!(table->map & usable_tables))
+      continue;
+
+    table->keys_usable_for_splitting.clear_all();
+    uint i;
+    for (i= 0; i < table->s->keys; i++)
+    {
+      if (!table->keys_in_use_for_query.is_set(i))
+        continue;
+      KEY *key_info= table->key_info + i;
+      uint key_parts= table->actual_n_key_parts(key_info);
+      uint usable_kp_cnt= 0;
+      for ( ; usable_kp_cnt < key_parts; usable_kp_cnt++)
+      {
+        if (key_info->actual_rec_per_key(usable_kp_cnt) == 0)
+          break;
+        int fldnr= key_info->key_part[usable_kp_cnt].fieldnr;
+
+        for (cand= cand_start; cand < cand_end; cand++)
+        {
+          if (cand->underlying_field->field_index + 1 == fldnr)
+	  {
+            cand->is_usable_for_ref_access= true;
+            break;
+          }
+        }
+        if (cand == cand_end)
+          break;
+      }
+      if (usable_kp_cnt)
+        table->keys_usable_for_splitting.set_bit(i);
+    }
+  }
+
+  /* Count the candidate fields that can be accessed by ref */
+  uint spl_field_cnt= candidates.elements();
+  for (cand= cand_start; cand < cand_end; cand++)
+  {
+    if (!cand->is_usable_for_ref_access)
+      spl_field_cnt--;
+  }
+
+  if (!spl_field_cnt)  // No candidate field can be accessed by ref => !(8)
+    return false;
+
+  /*
+    Create a structure of the type SplM_opt_info and fill it with
+    the collected info on potential splittability of T
+  */
+  SplM_opt_info *spl_opt_info= new (thd->mem_root) SplM_opt_info();
+  SplM_field_info *spl_field=
+    (SplM_field_info *) (thd->calloc(sizeof(SplM_field_info) *
+                                            spl_field_cnt));
+
+  if (!(spl_opt_info && spl_field)) // consider T as not good for splitting
+    return false;
+
+  spl_opt_info->join= this;
+  spl_opt_info->tables_usable_for_splitting= 0;
+  spl_opt_info->spl_field_cnt= spl_field_cnt;
+  spl_opt_info->spl_fields= spl_field;
+  for (cand= cand_start; cand < cand_end; cand++)
+  {
+    if (!cand->is_usable_for_ref_access)
+      continue;
+    spl_field->producing_item= cand->producing_item;
+    spl_field->underlying_field= cand->underlying_field;
+    spl_field->mat_field= cand->mat_field;
+    spl_opt_info->tables_usable_for_splitting|=
+    cand->underlying_field->table->map;
+    spl_field++;
+  }
+
+  /* Attach this info to the table T */
+  derived->table->set_spl_opt_info(spl_opt_info);
+
+  return true;
+}
+
+
+/**
+  @brief
+    Collect info on KEY_FIELD usable for splitting
+
+  @param
+    key_field     KEY_FIELD to collect info on
+
+  @details
+    The function assumes that this table is potentially splittable.
+    The function checks whether the KEY_FIELD structure key_field built for
+    this  table was created for a splitting field f. If so, the function does
+    the following using info from key_field:
+    1. Builds an equality of the form f = key_field->val that could be
+       pushed into this table.
+    2. Creates a new KEY_FIELD structure for this equality and stores
+       a reference to this structure in this->spl_opt_info.
+*/
+
+void TABLE::add_splitting_info_for_key_field(KEY_FIELD *key_field)
+{
+  DBUG_ASSERT(spl_opt_info != NULL);
+  JOIN *join= spl_opt_info->join;
+  Field *field= key_field->field;
+  SplM_field_info *spl_field= spl_opt_info->spl_fields;
+  uint i= spl_opt_info->spl_field_cnt;
+  for ( ; i; i--, spl_field++)
+  {
+    if (spl_field->mat_field == field)
+      break;
+  }
+  if (!i)         // field is not usable for splitting
+    return;
+
+  /*
+    Any equality condition that can be potentially pushed into the
+    materialized derived table is constructed now though later it may turn out
+    that it is not needed, because it is not used for splitting.
+    The reason for this is that the failure to construct it when it has to be
+    injected causes denial for further processing of the query.
+    Formally this equality is needed in the KEY_FIELD structure constructed
+    here that will be used to generate additional keyuses usable for splitting.
+    However key_field.cond could be used for this purpose (see implementations
+    of virtual function can_optimize_keypart_ref()).
+
+    The condition is built in such a form that it can be added to the WHERE
+    condition of the select that specifies this table.
+  */
+  THD *thd= in_use;
+  Item *left_item= spl_field->producing_item->build_clone(thd);
+  Item *right_item= key_field->val->build_clone(thd);
+  Item_func_eq *eq_item= 0;
+  if (left_item && right_item)
+  {
+    right_item->walk(&Item::set_fields_as_dependent_processor,
+                     false, join->select_lex);
+    right_item->update_used_tables();
+    eq_item= new (thd->mem_root) Item_func_eq(thd, left_item, right_item);
+  }
+  if (!eq_item)
+    return;
+  KEY_FIELD *added_key_field=
+    (KEY_FIELD *) thd->alloc(sizeof(KEY_FIELD));
+  if (!added_key_field ||
+      spl_opt_info->added_key_fields.push_back(added_key_field,thd->mem_root))
+    return;
+  added_key_field->field= spl_field->underlying_field;
+  added_key_field->cond= eq_item;
+  added_key_field->val= key_field->val;
+  added_key_field->level= 0;
+  added_key_field->optimize= KEY_OPTIMIZE_EQ;
+  added_key_field->eq_func= true;
+  added_key_field->null_rejecting= true;
+  added_key_field->cond_guard= NULL;
+  added_key_field->sj_pred_no= UINT_MAX;
+  return;
+}
+
+
+static bool
+add_ext_keyuse_for_splitting(Dynamic_array<KEYUSE_EXT> *ext_keyuses,
+                             KEY_FIELD *added_key_field, uint key, uint part)
+{
+  KEYUSE_EXT keyuse_ext;
+  Field *field= added_key_field->field;
+
+  JOIN_TAB *tab=field->table->reginfo.join_tab;
+  key_map possible_keys=field->get_possible_keys();
+  possible_keys.intersect(field->table->keys_usable_for_splitting);
+  tab->keys.merge(possible_keys);
+
+  Item_func_eq *eq_item= (Item_func_eq *) (added_key_field->cond);
+  keyuse_ext.table= field->table;
+  keyuse_ext.val= eq_item->arguments()[1];
+  keyuse_ext.key= key;
+  keyuse_ext.keypart=part;
+  keyuse_ext.keypart_map= (key_part_map) 1 << part;
+  keyuse_ext.used_tables= keyuse_ext.val->used_tables();
+  keyuse_ext.optimize= added_key_field->optimize & KEY_OPTIMIZE_REF_OR_NULL;
+  keyuse_ext.ref_table_rows= 0;
+  keyuse_ext.null_rejecting= added_key_field->null_rejecting;
+  keyuse_ext.cond_guard= added_key_field->cond_guard;
+  keyuse_ext.sj_pred_no= added_key_field->sj_pred_no;
+  keyuse_ext.validity_ref= 0;
+  keyuse_ext.needed_in_prefix= added_key_field->val->used_tables();
+  keyuse_ext.validity_var= false;
+  return ext_keyuses->push(keyuse_ext);
+}
+
+
+static int
+sort_ext_keyuse(KEYUSE_EXT *a, KEYUSE_EXT *b)
+{
+  if (a->table->tablenr != b->table->tablenr)
+    return (int) (a->table->tablenr - b->table->tablenr);
+  if (a->key != b->key)
+    return (int) (a->key - b->key);
+  return (int) (a->keypart - b->keypart);
+}
+
+
+static void
+sort_ext_keyuses(Dynamic_array<KEYUSE_EXT> *keyuses)
+{
+  KEYUSE_EXT *first_keyuse= &keyuses->at(0);
+  my_qsort(first_keyuse, keyuses->elements(), sizeof(KEYUSE_EXT),
+           (qsort_cmp) sort_ext_keyuse);
+}
+
+
+/**
+  @brief
+    Add info on keyuses usable for splitting into an array
+*/
+
+static bool
+add_ext_keyuses_for_splitting_field(Dynamic_array<KEYUSE_EXT> *ext_keyuses,
+                                    KEY_FIELD *added_key_field)
+{
+  Field *field= added_key_field->field;
+  TABLE *table= field->table;
+  for (uint key= 0; key < table->s->keys; key++)
+  {
+    if (!(table->keys_usable_for_splitting.is_set(key)))
+      continue;
+    KEY *key_info= table->key_info + key;
+    uint key_parts= table->actual_n_key_parts(key_info);
+    KEY_PART_INFO *key_part_info= key_info->key_part;
+    for (uint part=0; part <  key_parts; part++, key_part_info++)
+    {
+      if (!field->eq(key_part_info->field))
+        continue;
+      if (add_ext_keyuse_for_splitting(ext_keyuses, added_key_field, key, part))
+        return true;
+    }
+  }
+  return false;
+}
+
+
+/*
+  @brief
+    Cost of the post join operation used in specification of splittable table
+*/
+
+static
+double spl_postjoin_oper_cost(THD *thd, double join_record_count, uint rec_len)
+{
+  double cost;
+  cost=  get_tmp_table_write_cost(thd, join_record_count,rec_len) *
+         join_record_count;   // cost to fill tmp table
+  cost+= get_tmp_table_lookup_cost(thd, join_record_count,rec_len) *
+         join_record_count;   // cost to perform post join operation used here
+  cost+= get_tmp_table_lookup_cost(thd, join_record_count, rec_len) +
+         join_record_count * log2 (join_record_count) *
+         SORT_INDEX_CMP_COST;             // cost to perform  sorting
+  return cost;
+}
+
+/**
+  @brief
+    Add KEYUSE structures that can be usable for splitting
+
+  @details
+    This function is called only for joins created for potentially
+    splittable materialized tables. The function does the following:
+    1. Creates the dynamic array ext_keyuses_for_splitting of KEYUSE_EXT
+       structures and fills is with info about all keyuses that
+       could be used for splitting.
+    2. Sort the array ext_keyuses_for_splitting for fast access by key
+       on certain columns.
+    3. Collects and stores cost and cardinality info on the best execution
+       plan that does not use splitting and save this plan together with
+       corresponding array of keyuses.
+    4. Expand this array with KEYUSE elements built from the info stored
+       in ext_keyuses_for_splitting that could be produced by pushed
+       equalities employed for splitting.
+    5. Prepare the extended array of keyuses to be used in the function
+       best_access_plan()
+*/
+
+void JOIN::add_keyuses_for_splitting()
+{
+  uint i;
+  uint idx;
+  KEYUSE_EXT *keyuse_ext;
+  KEYUSE_EXT keyuse_ext_end;
+  double oper_cost;
+  uint rec_len;
+  uint added_keyuse_count;
+  TABLE *table= select_lex->master_unit()->derived->table;
+  List_iterator_fast<KEY_FIELD> li(spl_opt_info->added_key_fields);
+  KEY_FIELD *added_key_field;
+  if (!spl_opt_info->added_key_fields.elements)
+    goto err;
+  if (!(ext_keyuses_for_splitting= new Dynamic_array<KEYUSE_EXT>))
+    goto err;
+  while ((added_key_field= li++))
+  {
+    (void) add_ext_keyuses_for_splitting_field(ext_keyuses_for_splitting,
+                                               added_key_field);
+  }
+  added_keyuse_count= ext_keyuses_for_splitting->elements();
+  if (!added_keyuse_count)
+    goto err;
+  sort_ext_keyuses(ext_keyuses_for_splitting);
+  bzero((char*) &keyuse_ext_end, sizeof(keyuse_ext_end));
+  if (ext_keyuses_for_splitting->push(keyuse_ext_end))
+    goto err;
+
+  spl_opt_info->unsplit_card= join_record_count;
+
+  rec_len= table->s->rec_buff_length;
+
+  oper_cost= spl_postjoin_oper_cost(thd, join_record_count, rec_len);
+
+  spl_opt_info->unsplit_cost= best_positions[table_count-1].read_time +
+                              oper_cost;
+
+  if (!(save_qep= new Join_plan_state(table_count + 1)))
+    goto err;
+
+  save_query_plan(save_qep);
+
+  if (!keyuse.buffer &&
+       my_init_dynamic_array(&keyuse, sizeof(KEYUSE), 20, 64,
+                             MYF(MY_THREAD_SPECIFIC)))
+    goto err;
+
+  if (allocate_dynamic(&keyuse,
+                       save_qep->keyuse.elements +
+                       added_keyuse_count))
+    goto err;
+
+  memcpy(keyuse.buffer,
+         save_qep->keyuse.buffer,
+         (size_t) save_qep->keyuse.elements * keyuse.size_of_element);
+  keyuse.elements= save_qep->keyuse.elements;
+
+  keyuse_ext= &ext_keyuses_for_splitting->at(0);
+  idx= save_qep->keyuse.elements;
+  for (i=0; i < added_keyuse_count; i++, keyuse_ext++, idx++)
+  {
+    set_dynamic(&keyuse, (KEYUSE *) keyuse_ext, idx);
+    KEYUSE *added_keyuse= ((KEYUSE *) (keyuse.buffer)) + idx;
+    added_keyuse->validity_ref= &keyuse_ext->validity_var;
+  }
+
+  if (sort_and_filter_keyuse(thd, &keyuse, true))
+    goto err;
+  optimize_keyuse(this, &keyuse);
+
+  for (uint i= 0; i < table_count; i++)
+  {
+    JOIN_TAB *tab= join_tab + i;
+    map2table[tab->table->tablenr]= tab;
+  }
+
+  return;
+
+err:
+  if (save_qep)
+    restore_query_plan(save_qep);
+  table->deny_splitting();
+  return;
+}
+
+
+/**
+  @brief
+    Add KEYUSE structures that can be usable for splitting of this joined table
+*/
+
+void JOIN_TAB::add_keyuses_for_splitting()
+{
+  DBUG_ASSERT(table->spl_opt_info != NULL);
+  SplM_opt_info *spl_opt_info= table->spl_opt_info;
+  spl_opt_info->join->add_keyuses_for_splitting();
+}
+
+
+/*
+  @brief
+     Find info on the splitting plan by the splitting key
+*/
+
+SplM_plan_info *SplM_opt_info::find_plan(TABLE *table, uint key, uint parts)
+{
+  List_iterator_fast<SplM_plan_info> li(plan_cache);
+  SplM_plan_info *spl_plan;
+  while ((spl_plan= li++))
+  {
+    if (spl_plan->table == table &&
+        spl_plan->key == key &&
+        spl_plan->parts == parts)
+      break;
+  }
+  return spl_plan;
+}
+
+
+/*
+  @breaf
+    Enable/Disable a keyuses that can be used for splitting
+ */
+
+static
+void reset_validity_vars_for_keyuses(KEYUSE_EXT *key_keyuse_ext_start,
+                                     TABLE *table, uint key,
+                                     table_map remaining_tables,
+                                     bool validity_val)
+{
+  KEYUSE_EXT *keyuse_ext= key_keyuse_ext_start;
+  do
+  {
+    if (!(keyuse_ext->needed_in_prefix & remaining_tables))
+    {
+      /*
+        The enabling/disabling flags are set just in KEYUSE_EXT structures.
+        Yet keyuses that are used by best_access_path() have pointers
+        to these flags.
+      */
+      keyuse_ext->validity_var= validity_val;
+    }
+    keyuse_ext++;
+  }
+  while (keyuse_ext->key == key && keyuse_ext->table == table);
+}
+
+
+/**
+  @brief
+    Choose the best splitting to extend the evaluated partial join
+
+  @param
+    record_count      estimated cardinality of the extended partial join
+    remaining_tables  tables not joined yet
+
+  @details
+    This function is called during the search for the best execution
+    plan of the join that contains this table T. The function is called
+    every time when the optimizer tries to extend a partial join by
+    joining it with table T. Depending on what tables are already in the
+    partial join different equalities usable for splitting can be pushed
+    into T. The function evaluates different variants and chooses the
+    best one. Then the function finds the plan for the materializing join
+    with the chosen equality conditions pushed into it. If the cost of the
+    plan turns out to be less than the cost of the best plan without
+    splitting the function set it as the true plan of materialization
+    of the table T.
+    The function caches the found plans for materialization of table T
+    together if the info what key was used for splitting. Next time when
+    the optimizer prefers to use the same key the plan is taken from
+    the cache of plans
+
+  @retval
+    Pointer to the info on the found plan that employs the pushed equalities
+    if the plan has been chosen, NULL - otherwise.
+*/
+
+SplM_plan_info * JOIN_TAB::choose_best_splitting(double record_count,
+                                                 table_map remaining_tables)
+{
+  SplM_opt_info *spl_opt_info= table->spl_opt_info;
+  DBUG_ASSERT(spl_opt_info != NULL);
+  JOIN *join= spl_opt_info->join;
+  THD *thd= join->thd;
+  table_map tables_usable_for_splitting=
+              spl_opt_info->tables_usable_for_splitting;
+  KEYUSE_EXT *keyuse_ext= &join->ext_keyuses_for_splitting->at(0);
+  KEYUSE_EXT *best_key_keyuse_ext_start;
+  TABLE *best_table= 0;
+  double best_rec_per_key= DBL_MAX;
+  SplM_plan_info *spl_plan= 0;
+  uint best_key;
+  uint best_key_parts;
+
+  /*
+    Check whether there are keys that can be used to join T employing splitting
+    and if so, select the best out of such keys
+  */
+  for (uint tablenr= 0; tablenr < join->table_count; tablenr++)
+  {
+    if (!((1 << tablenr) & tables_usable_for_splitting))
+      continue;
+    JOIN_TAB *tab= join->map2table[tablenr];
+    TABLE *table= tab->table;
+    do
+    {
+      uint key= keyuse_ext->key;
+      KEYUSE_EXT *key_keyuse_ext_start= keyuse_ext;
+      key_part_map found_parts= 0;
+      do
+      {
+        if (keyuse_ext->needed_in_prefix & remaining_tables)
+	{
+          keyuse_ext++;
+          continue;
+        }
+        if (!(keyuse_ext->keypart_map & found_parts))
+	{
+          if ((!found_parts && !keyuse_ext->keypart) ||
+              (found_parts && ((keyuse_ext->keypart_map >> 1) & found_parts)))
+            found_parts|= keyuse_ext->keypart_map;
+          else
+	  {
+            do
+	    {
+              keyuse_ext++;
+            }
+            while (keyuse_ext->key == key && keyuse_ext->table == table);
+            break;
+          }
+        }
+        KEY *key_info= table->key_info + key;
+        double rec_per_key=
+                 key_info->actual_rec_per_key(keyuse_ext->keypart);
+        if (rec_per_key < best_rec_per_key)
+	{
+          best_table= keyuse_ext->table;
+          best_key= keyuse_ext->key;
+	  best_key_parts= keyuse_ext->keypart + 1;
+          best_rec_per_key= rec_per_key;
+          best_key_keyuse_ext_start= key_keyuse_ext_start;
+        }
+        keyuse_ext++;
+      }
+      while (keyuse_ext->key == key && keyuse_ext->table == table);
+    }
+    while (keyuse_ext->table == table);
+  }
+  if (best_table)
+  {
+    /*
+      The key for splitting was chosen, look for the plan for this key
+      in the cache
+    */
+    spl_plan= spl_opt_info->find_plan(best_table, best_key, best_key_parts);
+    if (!spl_plan &&
+	(spl_plan= (SplM_plan_info *) thd->alloc(sizeof(SplM_plan_info))) &&
+	(spl_plan->best_positions=
+	   (POSITION *) thd->alloc(sizeof(POSITION) * join->table_count)) &&
+	!spl_opt_info->plan_cache.push_back(spl_plan))
+    {
+      /*
+        The plan for the chosen key has not been found in the cache.
+        Build a new plan and save info on it in the cache
+      */
+      table_map all_table_map= (1 << join->table_count) - 1;
+      reset_validity_vars_for_keyuses(best_key_keyuse_ext_start, best_table,
+                                      best_key, remaining_tables, true);
+      choose_plan(join, all_table_map & ~join->const_table_map);
+      spl_plan->keyuse_ext_start= best_key_keyuse_ext_start;
+      spl_plan->table= best_table;
+      spl_plan->key= best_key;
+      spl_plan->parts= best_key_parts;
+      spl_plan->split_sel= best_rec_per_key / spl_opt_info->unsplit_card;
+
+      uint rec_len= table->s->rec_buff_length;
+
+      double split_card= spl_opt_info->unsplit_card * spl_plan->split_sel;
+      double oper_cost= split_card *
+                        spl_postjoin_oper_cost(thd, split_card, rec_len);
+      spl_plan->cost= join->best_positions[join->table_count-1].read_time +
+                      + oper_cost;
+
+      memcpy((char *) spl_plan->best_positions,
+             (char *) join->best_positions,
+             sizeof(POSITION) * join->table_count);
+      reset_validity_vars_for_keyuses(best_key_keyuse_ext_start, best_table,
+                                      best_key, remaining_tables, false);
+    }
+    spl_opt_info->last_plan= 0;
+    if (spl_plan)
+    {
+      if(record_count * spl_plan->cost < spl_opt_info->unsplit_cost)
+      {
+        /*
+          The best plan that employs splitting is cheaper than
+          the plan without splitting
+	*/
+        spl_opt_info->last_plan= spl_plan;
+      }
+    }
+  }
+
+  /* Set the cost of the preferred materialization for this partial join */
+  records= spl_opt_info->unsplit_card;
+  spl_plan= spl_opt_info->last_plan;
+  if (spl_plan)
+  {
+    startup_cost= record_count * spl_plan->cost;
+    records= (ha_rows) (records * spl_plan->split_sel);
+  }
+  else
+    startup_cost= spl_opt_info->unsplit_cost;
+  return spl_plan;
+}
+
+
+/**
+  @brief
+    Inject equalities for splitting used by the materialization join
+
+  @param
+    remaining_tables  used to filter out the equalities that cannot
+                      be pushed.
+
+  @details
+    This function is called by JOIN_TAB::fix_splitting that is used
+    to fix the chosen splitting of a splittable materialized table T
+    in the final query execution plan. In this plan the table T
+    is joined just before the 'remaining_tables'. So all equalities
+    usable for splitting whose right parts do not depend on any of
+    remaining tables can be pushed into join for T.
+    The function also marks the select that specifies T as
+    UNCACHEABLE_DEPENDENT_INJECTED.
+
+  @retval
+    false  on success
+    true   on failure
+*/
+
+bool JOIN::inject_best_splitting_cond(table_map remaining_tables)
+{
+  Item *inj_cond= 0;
+  List<Item> inj_cond_list;
+  List_iterator<KEY_FIELD> li(spl_opt_info->added_key_fields);
+  KEY_FIELD *added_key_field;
+  while ((added_key_field= li++))
+  {
+    if (remaining_tables & added_key_field->val->used_tables())
+      continue;
+    if (inj_cond_list.push_back(added_key_field->cond, thd->mem_root))
+      return true;
+  }
+  DBUG_ASSERT(inj_cond_list.elements);
+  switch (inj_cond_list.elements) {
+  case 1:
+    inj_cond= inj_cond_list.head(); break;
+  default:
+    inj_cond= new (thd->mem_root) Item_cond_and(thd, inj_cond_list);
+    if (!inj_cond)
+      return true;
+  }
+  if (inj_cond)
+    inj_cond->fix_fields(thd,0);
+
+  if (inject_cond_into_where(inj_cond))
+    return true;
+
+  select_lex->uncacheable|= UNCACHEABLE_DEPENDENT_INJECTED;
+  st_select_lex_unit *unit= select_lex->master_unit();
+  unit->uncacheable|= UNCACHEABLE_DEPENDENT_INJECTED;
+
+  return false;
+}
+
+
+/**
+  @brief
+    Fix the splitting chosen for a splittable table in the final query plan
+
+  @param
+    spl_plan   info on the splitting plan chosen for the splittable table T
+    remaining_tables  the table T is joined just before these tables
+
+  @details
+    If in the final query plan the optimizer has chosen a splitting plan
+    then the function sets this plan as the final execution plan to
+    materialized the table T. Otherwise the plan that does not use
+    splitting is set for the materialization.
+
+  @retval
+    false  on success
+    true   on failure
+*/
+
+bool JOIN_TAB::fix_splitting(SplM_plan_info *spl_plan,
+                             table_map remaining_tables)
+{
+  SplM_opt_info *spl_opt_info= table->spl_opt_info;
+  DBUG_ASSERT(table->spl_opt_info != 0);
+  JOIN *md_join= spl_opt_info->join;
+  if (spl_plan)
+  {
+    memcpy((char *) md_join->best_positions,
+           (char *) spl_plan->best_positions,
+           sizeof(POSITION) * md_join->table_count);
+    if (md_join->inject_best_splitting_cond(remaining_tables))
+      return true;
+    /*
+      This is called for a proper work of JOIN::get_best_combination()
+      called for the join that materializes T
+    */
+    reset_validity_vars_for_keyuses(spl_plan->keyuse_ext_start,
+                                    spl_plan->table,
+                                    spl_plan->key,
+                                    remaining_tables,
+                                    true);
+  }
+  else
+  {
+    md_join->restore_query_plan(md_join->save_qep);
+  }
+  return false;
+}
+
+
+/**
+  @brief
+    Fix the splittings chosen splittable tables in the final query plan
+
+  @details
+    The function calls JOIN_TAB::fix_splittins for all potentially
+    splittable tables in this join to set all final materialization
+    plans chosen for these tables.
+
+  @retval
+    false  on success
+    true   on failure
+*/
+
+bool JOIN::fix_all_splittings_in_plan()
+{
+  table_map prev_tables= 0;
+  table_map all_tables= (1 << table_count) - 1;
+  for (uint tablenr=0 ; tablenr < table_count ; tablenr++)
+  {
+    POSITION *cur_pos= &best_positions[tablenr];
+    JOIN_TAB *tab= cur_pos->table;
+    if (tab->table->is_splittable())
+    {
+      SplM_plan_info *spl_plan= cur_pos->spl_plan;
+      if (tab->fix_splitting(spl_plan, all_tables & ~prev_tables))
+          return true;
+    }
+    prev_tables|= tab->table->map;
+  }
+  return false;
+}
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index 9cd2eedb55d..dada23508b5 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -446,10 +446,6 @@ static bool convert_subq_to_jtbm(JOIN *parent_join,
                                  Item_in_subselect *subq_pred, bool *remove);
 static TABLE_LIST *alloc_join_nest(THD *thd);
 static uint get_tmp_table_rec_length(Ref_ptr_array p_list, uint elements);
-static double get_tmp_table_lookup_cost(THD *thd, double row_count,
-                                        uint row_size);
-static double get_tmp_table_write_cost(THD *thd, double row_count,
-                                       uint row_size);
 bool find_eq_ref_candidate(TABLE *table, table_map sj_inner_tables);
 static SJ_MATERIALIZATION_INFO *
 at_sjmat_pos(const JOIN *join, table_map remaining_tables, const JOIN_TAB *tab,
@@ -2468,7 +2464,7 @@ static uint get_tmp_table_rec_length(Ref_ptr_array p_items, uint elements)
   @return  the cost of one lookup
 */
 
-static double
+double
 get_tmp_table_lookup_cost(THD *thd, double row_count, uint row_size)
 {
   if (row_count * row_size > thd->variables.max_heap_table_size)
@@ -2488,7 +2484,7 @@ get_tmp_table_lookup_cost(THD *thd, double row_count, uint row_size)
   @return  the cost of writing one row
 */
 
-static double
+double
 get_tmp_table_write_cost(THD *thd, double row_count, uint row_size)
 {
   double lookup_cost= get_tmp_table_lookup_cost(thd, row_count, row_size);
diff --git a/sql/sql_class.h b/sql/sql_class.h
index de56206df4b..8747d658aea 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -5507,8 +5507,6 @@ public:
 };
 
 
-
-
 /*
   Optimizer and executor structure for the materialized semi-join info. This
   structure contains
diff --git a/sql/sql_const.h b/sql/sql_const.h
index 007b7faeebb..65742235bee 100644
--- a/sql/sql_const.h
+++ b/sql/sql_const.h
@@ -231,6 +231,7 @@
 */
 #define HEAP_TEMPTABLE_LOOKUP_COST 0.05
 #define DISK_TEMPTABLE_LOOKUP_COST 1.0
+#define SORT_INDEX_CMP_COST 0.02
 
 #define MY_CHARSET_BIN_MB_MAXLEN 1
 
diff --git a/sql/sql_priv.h b/sql/sql_priv.h
index f40ac6f2663..ba37d933f12 100644
--- a/sql/sql_priv.h
+++ b/sql/sql_priv.h
@@ -227,7 +227,7 @@
 #define OPTIMIZER_SWITCH_EXISTS_TO_IN              (1ULL << 28)
 #define OPTIMIZER_SWITCH_ORDERBY_EQ_PROP           (1ULL << 29)
 #define OPTIMIZER_SWITCH_COND_PUSHDOWN_FOR_DERIVED (1ULL << 30)
-#define OPTIMIZER_SWITCH_SPLIT_GROUPING_DERIVED    (1ULL << 31)
+#define OPTIMIZER_SWITCH_SPLIT_MATERIALIZED        (1ULL << 31)
 
 #define OPTIMIZER_SWITCH_DEFAULT   (OPTIMIZER_SWITCH_INDEX_MERGE | \
                                     OPTIMIZER_SWITCH_INDEX_MERGE_UNION | \
@@ -254,7 +254,7 @@
                                     OPTIMIZER_SWITCH_EXISTS_TO_IN | \
                                     OPTIMIZER_SWITCH_ORDERBY_EQ_PROP | \
                                     OPTIMIZER_SWITCH_COND_PUSHDOWN_FOR_DERIVED | \
-                                    OPTIMIZER_SWITCH_SPLIT_GROUPING_DERIVED)
+                                    OPTIMIZER_SWITCH_SPLIT_MATERIALIZED)
 
 /*
   Replication uses 8 bytes to store SQL_MODE in the binary log. The day you
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 96aa227b439..82539865d41 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -85,7 +85,6 @@ LEX_CSTRING distinct_key= {STRING_WITH_LEN("distinct_key")};
 
 struct st_sargable_param;
 
-static void optimize_keyuse(JOIN *join, DYNAMIC_ARRAY *keyuse_array);
 static bool make_join_statistics(JOIN *join, List<TABLE_LIST> &leaves, 
                                  DYNAMIC_ARRAY *keyuse);
 static bool update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,
@@ -93,8 +92,6 @@ static bool update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,
                                 uint tables, COND *conds,
                                 table_map table_map, SELECT_LEX *select_lex,
                                 SARGABLE_PARAM **sargables);
-static bool sort_and_filter_keyuse(THD *thd, DYNAMIC_ARRAY *keyuse,
-                                   bool skip_unprefixed_keyparts);
 static int sort_keyuse(KEYUSE *a,KEYUSE *b);
 static bool are_tables_local(JOIN_TAB *jtab, table_map used_tables);
 static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse,
@@ -1125,7 +1122,6 @@ int JOIN::optimize()
     if (optimization_state != JOIN::NOT_OPTIMIZED)
       return FALSE;
     optimization_state= JOIN::OPTIMIZATION_IN_PROGRESS;
-    is_for_splittable_grouping_derived= false;
     res= optimize_inner();
   }
   if (!with_two_phase_optimization ||
@@ -1571,9 +1567,6 @@ int JOIN::optimize_stage2()
   if (subq_exit_fl)
     goto setup_subq_exit;
 
-  if (select_lex->handle_derived(thd->lex, DT_OPTIMIZE))
-    DBUG_RETURN(1);
-
   if (thd->check_killed())
     DBUG_RETURN(1);
   
@@ -1581,6 +1574,9 @@ int JOIN::optimize_stage2()
   if (get_best_combination())
     DBUG_RETURN(1);
   
+  if (select_lex->handle_derived(thd->lex, DT_OPTIMIZE))
+    DBUG_RETURN(1);
+
   if (optimizer_flag(thd, OPTIMIZER_SWITCH_DERIVED_WITH_KEYS))
     drop_unused_derived_keys();
 
@@ -3670,7 +3666,11 @@ JOIN::destroy()
   cleanup_item_list(tmp_all_fields1);
   cleanup_item_list(tmp_all_fields3);
   destroy_sj_tmp_tables(this);
-  delete_dynamic(&keyuse); 
+  delete_dynamic(&keyuse);
+  if (save_qep)
+    delete(save_qep);
+  if (ext_keyuses_for_splitting)
+    delete(ext_keyuses_for_splitting);
   delete procedure;
   DBUG_RETURN(error);
 }
@@ -4124,6 +4124,12 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list,
     DBUG_EXECUTE("opt", print_keyuse_array(keyuse_array););
   }
 
+  for (s= stat; s < stat_end; s++)
+  {
+    if (s->table->is_splittable())
+      s->add_keyuses_for_splitting();
+  }
+
   join->const_table_map= no_rows_const_tables;
   join->const_tables= const_count;
   eliminate_tables(join);
@@ -4597,9 +4603,6 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list,
   if (join->choose_subquery_plan(all_table_map & ~join->const_table_map))
     goto error;
 
-  if (join->improve_chosen_plan(join->thd))
-    goto error;
-  
   DEBUG_SYNC(join->thd, "inside_make_join_statistics");
 
   DBUG_RETURN(0);
@@ -4629,23 +4632,6 @@ error:
 	  keyuse     Pointer to possible keys
 *****************************************************************************/
 
-/// Used when finding key fields
-struct KEY_FIELD {
-  Field		*field;
-  Item_bool_func *cond;
-  Item		*val;			///< May be empty if diff constant
-  uint		level;
-  uint		optimize;
-  bool		eq_func;
-  /**
-    If true, the condition this struct represents will not be satisfied
-    when val IS NULL.
-  */
-  bool          null_rejecting; 
-  bool         *cond_guard; /* See KEYUSE::cond_guard */
-  uint          sj_pred_no; /* See KEYUSE::sj_pred_no */
-};
-
 
 /**
   Merge new key definitions to old ones, remove those not used in both.
@@ -5296,7 +5282,7 @@ Item_func_ne::add_key_fields(JOIN *join, KEY_FIELD **key_fields,
     /*
       QQ: perhaps test for !is_local_field(args[1]) is not really needed here.
       Other comparison functions, e.g. Item_func_le, Item_func_gt, etc,
-      do not have this test. See Item_bool_func2::add_key_field_optimize_op().
+      do not have this test. See Item_bool_func2::add_key_fieldoptimize_op().
       Check with the optimizer team.
     */
     if (is_local_field(args[0]) && !is_local_field(args[1]))
@@ -5479,6 +5465,7 @@ add_keyuse(DYNAMIC_ARRAY *keyuse_array, KEY_FIELD *key_field,
   keyuse.null_rejecting= key_field->null_rejecting;
   keyuse.cond_guard= key_field->cond_guard;
   keyuse.sj_pred_no= key_field->sj_pred_no;
+  keyuse.validity_ref= 0;
   return (insert_dynamic(keyuse_array,(uchar*) &keyuse));
 }
 
@@ -5524,7 +5511,9 @@ add_key_part(DYNAMIC_ARRAY *keyuse_array, KEY_FIELD *key_field)
         key_field->val->used_tables())
     {
       if (!field->can_optimize_hash_join(key_field->cond, key_field->val))
-        return false;      
+        return false;
+      if (form->is_splittable())
+        form->add_splitting_info_for_key_field(key_field);
       /* 
         If a key use is extracted from an equi-join predicate then it is
         added not only as a key use for every index whose component can
@@ -5538,7 +5527,6 @@ add_key_part(DYNAMIC_ARRAY *keyuse_array, KEY_FIELD *key_field)
   return FALSE;
 }
 
-
 static bool
 add_ft_keys(DYNAMIC_ARRAY *keyuse_array,
             JOIN_TAB *stat,COND *cond,table_map usable_tables)
@@ -5600,6 +5588,7 @@ add_ft_keys(DYNAMIC_ARRAY *keyuse_array,
   keyuse.optimize= 0;
   keyuse.keypart_map= 0;
   keyuse.sj_pred_no= UINT_MAX;
+  keyuse.validity_ref= 0;
   return insert_dynamic(keyuse_array,(uchar*) &keyuse);
 }
 
@@ -5887,8 +5876,8 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab,
   Special treatment for ft-keys.
 */
 
-static bool sort_and_filter_keyuse(THD *thd, DYNAMIC_ARRAY *keyuse, 
-                                   bool skip_unprefixed_keyparts)
+bool sort_and_filter_keyuse(THD *thd, DYNAMIC_ARRAY *keyuse, 
+                            bool skip_unprefixed_keyparts)
 {
   KEYUSE key_end, *prev, *save_pos, *use;
   uint found_eq_constant, i;
@@ -5956,7 +5945,7 @@ static bool sort_and_filter_keyuse(THD *thd, DYNAMIC_ARRAY *keyuse,
   Update some values in keyuse for faster choose_plan() loop.
 */
 
-static void optimize_keyuse(JOIN *join, DYNAMIC_ARRAY *keyuse_array)
+void optimize_keyuse(JOIN *join, DYNAMIC_ARRAY *keyuse_array)
 {
   KEYUSE *end,*keyuse= dynamic_element(keyuse_array, 0, KEYUSE*);
 
@@ -5997,7 +5986,6 @@ static void optimize_keyuse(JOIN *join, DYNAMIC_ARRAY *keyuse_array)
 }
 
 
-
 /**
   Check for the presence of AGGFN(DISTINCT a) queries that may be subject
   to loose index scan.
@@ -6305,6 +6293,7 @@ best_access_path(JOIN      *join,
   bool best_uses_jbuf= FALSE;
   MY_BITMAP *eq_join_set= &s->table->eq_join_set;
   KEYUSE *hj_start_key= 0;
+  SplM_plan_info *spl_plan= 0;
 
   disable_jbuf= disable_jbuf || idx == join->const_tables;  
 
@@ -6314,7 +6303,10 @@ best_access_path(JOIN      *join,
   bitmap_clear_all(eq_join_set);
 
   loose_scan_opt.init(join, s, remaining_tables);
-  
+
+  if (s->table->is_splittable())
+    spl_plan= s->choose_best_splitting(record_count, remaining_tables);
+
   if (s->keyuse)
   {                                            /* Use key if possible */
     KEYUSE *keyuse;
@@ -6379,6 +6371,7 @@ best_access_path(JOIN      *join,
                2. we won't get two ref-or-null's
           */
           if (!(remaining_tables & keyuse->used_tables) &&
+              (!keyuse->validity_ref || *keyuse->validity_ref) &&
               s->access_from_tables_is_allowed(keyuse->used_tables,
                                                join->sjm_lookup_tables) &&
               !(ref_or_null_part && (keyuse->optimize &
@@ -6691,6 +6684,7 @@ best_access_path(JOIN      *join,
         tmp += s->startup_cost;
         loose_scan_opt.check_ref_access_part2(key, start_key, records, tmp);
       } /* not ft_key */
+
       if (tmp + 0.0001 < best_time - records/(double) TIME_FOR_COMPARE)
       {
         best_time= tmp + records/(double) TIME_FOR_COMPARE;
@@ -6878,6 +6872,7 @@ best_access_path(JOIN      *join,
   pos->ref_depend_map= best_ref_depends_map;
   pos->loosescan_picker.loosescan_key= MAX_KEY;
   pos->use_join_buffer= best_uses_jbuf;
+  pos->spl_plan= spl_plan;
    
   loose_scan_opt.save_to_position(s, loose_scan_pos);
 
@@ -8932,192 +8927,15 @@ JOIN_TAB *next_depth_first_tab(JOIN* join, JOIN_TAB* tab)
   return tab;
 }
 
-static
-bool key_can_be_used_to_split_by_fields(KEY *key_info, uint used_key_parts,
-                                        List<Field> &fields)
-{ 
-  if (used_key_parts < fields.elements)
-    return false;
-  List_iterator_fast<Field> li(fields);
-  Field *fld;
-  KEY_PART_INFO *start= key_info->key_part;
-  KEY_PART_INFO *end= start + fields.elements;
-  while ((fld= li++))
-  {
-    KEY_PART_INFO *key_part;
-    for (key_part= start; key_part < end; key_part++)
-    {
-      if (key_part->fieldnr == fld->field_index + 1)
-        break;
-    }
-    if (key_part == end)
-      return false;
-  }
-  return true;
-}
-
-bool JOIN::check_for_splittable_grouping_derived(THD *thd)
-{
-  partition_list= 0;
-  st_select_lex_unit *unit= select_lex->master_unit();
-  TABLE_LIST *derived= unit->derived;
-  if (!optimizer_flag(thd, OPTIMIZER_SWITCH_SPLIT_GROUPING_DERIVED))
-    return false;
-  if (!(derived && derived->is_materialized_derived()))
-    return false;
-  if (unit->first_select()->next_select())
-    return false;
-  if (derived->prohibit_cond_pushdown)
-    return false;
-  if (derived->is_recursive_with_table())
-    return false;
-  if (group_list)
-  {
-    if (!select_lex->have_window_funcs())
-      partition_list= group_list;
-  }
-  else if (select_lex->have_window_funcs() &&
-           select_lex->window_specs.elements == 1)
-  {
-    partition_list=
-      select_lex->window_specs.head()->partition_list->first;
-  }
-  if (!partition_list)
-    return false;
- 
-  ORDER *ord;
-  TABLE *table= 0;
-  key_map ref_keys;
-  uint group_fields= 0;
-  ref_keys.set_all();
-  for (ord= partition_list; ord; ord= ord->next, group_fields++)
-  {
-    Item *ord_item= *ord->item;
-    if (ord_item->real_item()->type() != Item::FIELD_ITEM)
-      return false;
-    Field *ord_field= ((Item_field *) (ord_item->real_item()))->field;
-    if (!table)
-      table= ord_field->table;
-    else if (table != ord_field->table)
-      return false;
-    ref_keys.intersect(ord_field->part_of_key);
-  }
-  if (ref_keys.is_clear_all())
-    return false;
- 
-  uint i;
-  List<Field> grouping_fields;
-  List<Field> splitting_fields;
-  List_iterator<Item> li(fields_list);
-  for (ord= partition_list; ord; ord= ord->next)
-  {
-    Item *item;
-    i= 0;
-    while ((item= li++))
-    {
-      if ((*ord->item)->eq(item, 0))
-	break;
-      i++;
-    }
-    if (!item) 
-      return false;  
-    if (splitting_fields.push_back(derived->table->field[i], thd->mem_root))
-      return false;
-    Item_field *ord_field= (Item_field *)(item->real_item());
-    if (grouping_fields.push_back(ord_field->field, thd->mem_root)) 
-      return false;
-    li.rewind();
-  }
-
-  for (i= 0; i < table->s->keys; i++)
-  {
-    if (!(ref_keys.is_set(i)))
-	continue;
-    KEY *key_info= table->key_info + i;
-    if (key_can_be_used_to_split_by_fields(key_info,
-                                           table->actual_n_key_parts(key_info),
-                                           grouping_fields))
-      break;
-  }
-  if (i == table->s->keys)
-    return false;
- 
-  derived->table->splitting_fields= splitting_fields;
-  is_for_splittable_grouping_derived= true;
-  return true;
-}
-
 
 bool JOIN::check_two_phase_optimization(THD *thd)
 {
-  if (!check_for_splittable_grouping_derived(thd))
-    return false;
-  return true;
+  if (check_for_splittable_materialized())
+    return true;
+  return false;
 }
   
 
-Item *JOIN_TAB::get_splitting_cond_for_grouping_derived(THD *thd)
-{
-  /* this is a stub */
-  TABLE_LIST *derived= table->pos_in_table_list;
-  st_select_lex *sel= derived->get_unit()->first_select();
-  Item *cond= 0;
-  table_map used_tables= OUTER_REF_TABLE_BIT;
-  POSITION *pos= join->best_positions;
-  for (; pos->table != this; pos++)
-  {
-    used_tables|= pos->table->table->map;
-  }
-
-  if (!pos->key)
-    return 0;
-
-  KEY *key_info= table->key_info + pos->key->key;
-  if (!key_can_be_used_to_split_by_fields(key_info,
-                                          key_info->user_defined_key_parts,
-                                          table->splitting_fields))
-    return 0;
-
-  create_ref_for_key(join, this, pos->key,
-                     false, used_tables);
-  List<Item> cond_list;
-  KEY_PART_INFO *start= key_info->key_part;
-  KEY_PART_INFO *end= start + table->splitting_fields.elements;
-  List_iterator_fast<Field> li(table->splitting_fields);
-  Field *fld= li++;
-  for (ORDER *ord= sel->join->partition_list; ord;
-       ord= ord->next, fld= li++)  
-  {
-    Item *left_item= (*ord->item)->build_clone(thd);
-    uint i= 0;
-    for (KEY_PART_INFO *key_part= start; key_part < end; key_part++, i++)
-    {
-      if (key_part->fieldnr == fld->field_index + 1)
-        break;
-    }
-    Item *right_item= ref.items[i]->build_clone(thd);
-    Item_func_eq *eq_item= 0;
-    right_item= right_item->build_clone(thd);
-    if (left_item && right_item)
-    {
-      right_item->walk(&Item::set_fields_as_dependent_processor,
-                       false, join->select_lex);
-      right_item->update_used_tables();
-      eq_item= new (thd->mem_root) Item_func_eq(thd, left_item, right_item);
-    }
-    if (!eq_item || cond_list.push_back(eq_item, thd->mem_root))
-      return 0;
-  }
-  switch (cond_list.elements) {
-  case 0: break;
-  case 1: cond= cond_list.head(); break;
-  default: cond= new (thd->mem_root) Item_cond_and(thd, cond_list);
-  }
-  if (cond)
-    cond->fix_fields(thd,0);
-  return cond; 
-}
-
 bool JOIN::inject_cond_into_where(Item *injected_cond)
 {
   Item *where_item= injected_cond;
@@ -9152,48 +8970,6 @@ bool JOIN::inject_cond_into_where(Item *injected_cond)
 
 }
 
-bool JOIN::push_splitting_cond_into_derived(THD *thd, Item *cond) 
-{
-  enum_reopt_result reopt_result= REOPT_NONE;
-  table_map all_table_map= 0;
-  for (JOIN_TAB *tab= join_tab;
-       tab < join_tab + top_join_tab_count; tab++)
-    all_table_map|= tab->table->map;
-  reopt_result= reoptimize(cond, all_table_map & ~const_table_map, NULL);
-  if (reopt_result == REOPT_ERROR)
-    return true;
-  if (inject_cond_into_where(cond))
-    return true;
-  if (cond->used_tables() & OUTER_REF_TABLE_BIT)
-  {
-    select_lex->uncacheable|= UNCACHEABLE_DEPENDENT_INJECTED;
-    st_select_lex_unit *unit= select_lex->master_unit();
-    unit->uncacheable|= UNCACHEABLE_DEPENDENT_INJECTED;
-  }
-  return false;
-}
-
-bool JOIN::improve_chosen_plan(THD *thd)
-{
-  for (JOIN_TAB *tab= join_tab + const_tables;
-       tab < join_tab + table_count; tab++)
-  {
-    TABLE_LIST *tbl= tab->table->pos_in_table_list;
-    if (tbl->is_materialized_derived())
-    {
-      st_select_lex *sel= tbl->get_unit()->first_select();
-      JOIN *derived_join= sel->join;
-      if (derived_join && derived_join->is_for_splittable_grouping_derived)
-      {
-        Item *cond= tab->get_splitting_cond_for_grouping_derived(thd);
-        if (cond && derived_join->push_splitting_cond_into_derived(thd, cond))
-          return true;
-      }
-    }
-  }
-  return false;
-}
-
 
 static Item * const null_ptr= NULL;
 
@@ -9262,6 +9038,9 @@ bool JOIN::get_best_combination()
   full_join=0;
   hash_join= FALSE;
 
+  if (fix_all_splittings_in_plan())
+    DBUG_RETURN(TRUE);
+
   fix_semijoin_strategies_for_picked_join_order(this);
    
   JOIN_TAB_RANGE *root_range;
@@ -9602,6 +9381,7 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j,
     do
     {
       if (!(~used_tables & keyuse->used_tables) &&
+          (!keyuse->validity_ref || *keyuse->validity_ref) &&
 	  j->keyuse_is_valid_for_access_in_chosen_plan(join, keyuse))
       {
         if  (are_tables_local(j, keyuse->val->used_tables()))
@@ -9672,6 +9452,7 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j,
     for (i=0 ; i < keyparts ; keyuse++,i++)
     {
       while (((~used_tables) & keyuse->used_tables) ||
+             (keyuse->validity_ref && !(*keyuse->validity_ref)) ||
 	     !j->keyuse_is_valid_for_access_in_chosen_plan(join, keyuse) ||
              keyuse->keypart == NO_KEYPART ||
 	     (keyuse->keypart != 
@@ -17660,7 +17441,6 @@ err:
 }
 
 
-
 /****************************************************************************/
 
 void *Virtual_tmp_table::operator new(size_t size, THD *thd) throw()
diff --git a/sql/sql_select.h b/sql/sql_select.h
index 95e9e943c26..e1958e8ce5c 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -73,9 +73,45 @@ typedef struct keyuse_t {
   */
   uint         sj_pred_no;
 
+  /*
+    If this is NULL than KEYUSE is always enabled.
+    Otherwise it points to the enabling flag for this keyuse (true <=> enabled)
+  */
+  bool *validity_ref;
+
   bool is_for_hash_join() { return is_hash_join_key_no(key); }
 } KEYUSE;
 
+
+struct KEYUSE_EXT: public KEYUSE
+{
+  /*
+    This keyuse can be used only when the partial join being extended
+    contains the tables from this table map
+  */
+  table_map needed_in_prefix;
+  /* The enabling flag for keyuses usable for splitting */
+  bool validity_var;
+};
+
+/// Used when finding key fields
+struct KEY_FIELD {
+  Field		*field;
+  Item_bool_func *cond;
+  Item		*val;			///< May be empty if diff constant
+  uint		level;
+  uint		optimize;
+  bool		eq_func;
+  /**
+    If true, the condition this struct represents will not be satisfied
+    when val IS NULL.
+  */
+  bool          null_rejecting;
+  bool         *cond_guard; /* See KEYUSE::cond_guard */
+  uint          sj_pred_no; /* See KEYUSE::sj_pred_no */
+};
+
+
 #define NO_KEYPART ((uint)(-1))
 
 class store_key;
@@ -201,6 +237,8 @@ class SJ_TMP_TABLE;
 class JOIN_TAB_RANGE;
 class AGGR_OP;
 class Filesort;
+struct SplM_plan_info;
+class SplM_opt_info;
 
 typedef struct st_join_table {
   st_join_table() {}
@@ -614,8 +652,10 @@ typedef struct st_join_table {
   bool use_order() const; ///< Use ordering provided by chosen index?
   bool sort_table();
   bool remove_duplicates();
-  Item *get_splitting_cond_for_grouping_derived(THD *thd);
-
+  void add_keyuses_for_splitting();
+  SplM_plan_info *choose_best_splitting(double record_count,
+                                        table_map remaining_tables);
+  bool fix_splitting(SplM_plan_info *spl_plan, table_map remaining_tables);
 } JOIN_TAB;
 
 
@@ -920,6 +960,9 @@ typedef struct st_position
   Firstmatch_picker         firstmatch_picker;
   LooseScan_picker          loosescan_picker;
   Sj_materialization_picker sjmat_picker;
+
+  /* Info on splitting plan used at this position */  
+  SplM_plan_info *spl_plan;
 } POSITION;
 
 typedef Bounds_checked_array<Item_null_result*> Item_null_array;
@@ -1053,11 +1096,13 @@ protected:
   /* Support for plan reoptimization with rewritten conditions. */
   enum_reopt_result reoptimize(Item *added_where, table_map join_tables,
                                Join_plan_state *save_to);
+  /* Choose a subquery plan for a table-less subquery. */
+  bool choose_tableless_subquery_plan();
+
+public:
   void save_query_plan(Join_plan_state *save_to);
   void reset_query_plan();
   void restore_query_plan(Join_plan_state *restore_from);
-  /* Choose a subquery plan for a table-less subquery. */
-  bool choose_tableless_subquery_plan();
 
 public:
   JOIN_TAB *join_tab, **best_ref;
@@ -1415,9 +1460,14 @@ public:
   */
   bool implicit_grouping; 
 
-  bool is_for_splittable_grouping_derived;
   bool with_two_phase_optimization;
-  ORDER *partition_list; 
+
+  /* Saved execution plan for this join */
+  Join_plan_state *save_qep;
+  /* Info on splittability of the table materialized by this plan*/
+  SplM_opt_info *spl_opt_info;
+  /* Contains info on keyuses usable for splitting */
+  Dynamic_array<KEYUSE_EXT> *ext_keyuses_for_splitting;
 
   JOIN_TAB *sort_and_group_aggr_tab;
 
@@ -1465,7 +1515,10 @@ public:
     need_distinct= 0;
     skip_sort_order= 0;
     with_two_phase_optimization= 0;
-    is_for_splittable_grouping_derived= 0;
+    save_qep= 0;
+    spl_opt_info= 0;
+    ext_keyuses_for_splitting= 0;
+    spl_opt_info= 0;
     need_tmp= 0;
     hidden_group_fields= 0; /*safety*/
     error= 0;
@@ -1674,10 +1727,12 @@ public:
                                const char *message);
   JOIN_TAB *first_breadth_first_tab() { return join_tab; }
   bool check_two_phase_optimization(THD *thd);
-  bool check_for_splittable_grouping_derived(THD *thd);
   bool inject_cond_into_where(Item *injected_cond);
-  bool push_splitting_cond_into_derived(THD *thd, Item *cond);
-  bool improve_chosen_plan(THD *thd);
+  bool check_for_splittable_materialized();
+  void add_keyuses_for_splitting();
+  bool inject_best_splitting_cond(table_map remaining_tables);
+  bool fix_all_splittings_in_plan();
+
   bool transform_in_predicates_into_in_subq(THD *thd);
 private:
   /**
@@ -2295,6 +2350,11 @@ bool open_tmp_table(TABLE *table);
 void setup_tmp_table_column_bitmaps(TABLE *table, uchar *bitmaps);
 double prev_record_reads(POSITION *positions, uint idx, table_map found_ref);
 void fix_list_after_tbl_changes(SELECT_LEX *new_parent, List<TABLE_LIST> *tlist);
+double get_tmp_table_lookup_cost(THD *thd, double row_count, uint row_size);
+double get_tmp_table_write_cost(THD *thd, double row_count, uint row_size);
+void optimize_keyuse(JOIN *join, DYNAMIC_ARRAY *keyuse_array);
+bool sort_and_filter_keyuse(THD *thd, DYNAMIC_ARRAY *keyuse,
+                            bool skip_unprefixed_keyparts);
 
 struct st_cond_statistic
 {
diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc
index 45e93116c03..0e5192d72ad 100644
--- a/sql/sys_vars.cc
+++ b/sql/sys_vars.cc
@@ -2457,7 +2457,7 @@ export const char *optimizer_switch_names[]=
   "exists_to_in",
   "orderby_uses_equalities",
   "condition_pushdown_for_derived",
-  "split_grouping_derived",
+  "split_materialized",
   "default", 
   NullS
 };
diff --git a/sql/table.h b/sql/table.h
index 8d66521b9ef..017db4883f3 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -1033,6 +1033,8 @@ struct st_cond_statistic;
 /* Bitmap of table's fields */
 typedef Bitmap<MAX_FIELDS> Field_map;
 
+class SplM_opt_info;
+
 struct TABLE
 {
   TABLE() {}                               /* Remove gcc warning */
@@ -1312,7 +1314,13 @@ public:
   bool stats_is_read;     /* Persistent statistics is read for the table */
   bool histograms_are_read;
   MDL_ticket *mdl_ticket;
-  List<Field> splitting_fields;
+
+  /*
+    This is used only for potentially splittable materialized tables and it
+    points to the info used by the optimizer to apply splitting optimization
+  */
+  SplM_opt_info *spl_opt_info;
+  key_map keys_usable_for_splitting;
 
   void init(THD *thd, TABLE_LIST *tl);
   bool fill_item_list(List<Item> *item_list) const;
@@ -1457,6 +1465,10 @@ public:
                                       bool with_cleanup);
   Field *find_field_by_name(LEX_CSTRING *str) const;
   bool export_structure(THD *thd, class Row_definition_list *defs);
+  bool is_splittable() { return spl_opt_info != NULL; }
+  void set_spl_opt_info(SplM_opt_info *spl_info);
+  void deny_splitting();
+  void add_splitting_info_for_key_field(struct KEY_FIELD *key_field);
 };
 
 
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 16b3571db9f..a80e1664663 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_grouping_derived=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
 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 961504412a9..96d681407fe 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_grouping_derived=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
 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 e5796f7a9b1..43737c7753e 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_grouping_derived=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
 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 3f1ed9971c3..1dcb1ee1b8b 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_grouping_derived=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
 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;